<!doctype html>



  


<html class="theme-next mist use-motion">
<head>
  <meta charset="UTF-8"/>
<meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1" />
<meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1"/>
<meta name="baidu-site-verification" content="zD47G9xe42" />


<meta http-equiv="Cache-Control" content="no-transform" />
<meta http-equiv="Cache-Control" content="no-siteapp" />












  
  
  <link href="/vendors/fancybox/source/jquery.fancybox.css?v=2.1.5" rel="stylesheet" type="text/css" />




  
  
  
  

  
    
    
  

  

  

  

  

  
    
    
    <link href="//fonts.googleapis.com/css?family=Lato:300,300italic,400,400italic,700,700italic&subset=latin,latin-ext" rel="stylesheet" type="text/css">
  






<link href="/vendors/font-awesome/css/font-awesome.min.css?v=4.4.0" rel="stylesheet" type="text/css" />

<link href="/css/main.css?v=5.0.1" rel="stylesheet" type="text/css" />


  <meta name="keywords" content="uiste个人博客网站,uiste,uiste博客,PHP,运维,Hexo,技术博客" />





  <link rel="alternate" href="/atom.xml" title="uiste" type="application/atom+xml" />




  <link rel="shortcut icon" type="image/x-icon" href="/favicon.ico?v=5.0.1" />






<meta name="description" content="Stay Hungry，Stay Foolish">
<meta property="og:type" content="website">
<meta property="og:title" content="uiste">
<meta property="og:url" content="http://blog.uiste.com/page/8/index.html">
<meta property="og:site_name" content="uiste">
<meta property="og:description" content="Stay Hungry，Stay Foolish">
<meta name="twitter:card" content="summary">
<meta name="twitter:title" content="uiste">
<meta name="twitter:description" content="Stay Hungry，Stay Foolish">



<script type="text/javascript" id="hexo.configuration">
  var NexT = window.NexT || {};
  var CONFIG = {
    scheme: 'Mist',
    sidebar: {"position":"left","display":"post"},
    fancybox: true,
    motion: true,
    duoshuo: {
      userId: 0,
      author: '博主'
    }
  };
</script>




  <link rel="canonical" href="http://blog.uiste.com/page/8/"/>

  <title>
 
  uiste - Stay Hungry，Stay Foolish 

</title>
</head>

<body itemscope itemtype="http://schema.org/WebPage" lang="zh-Hans">

  



  <script type="text/javascript">
    var _hmt = _hmt || [];
    (function() {
      var hm = document.createElement("script");
      hm.src = "//hm.baidu.com/hm.js?b58b87af012832b8ae35e33bb45b44fa";
      var s = document.getElementsByTagName("script")[0];
      s.parentNode.insertBefore(hm, s);
    })();
  </script>








  
  
    
  

  <div class="container one-collumn sidebar-position-left 
   page-home 
 ">
    <div class="headband"></div>

    <header id="header" class="header" itemscope itemtype="http://schema.org/WPHeader">
      <div class="header-inner"><div class="site-meta ">
  

  <div class="custom-logo-site-title">
    <a href="/"  class="brand" rel="start">
      <span class="logo-line-before"><i></i></span>
      <span class="site-title">uiste</span>
      <span class="logo-line-after"><i></i></span>
    </a>
  </div>
  <p class="site-subtitle">uiste个人博客</p>
</div>

<div class="site-nav-toggle">
  <button>
    <span class="btn-bar"></span>
    <span class="btn-bar"></span>
    <span class="btn-bar"></span>
  </button>
</div>

<nav class="site-nav">
  

  
    <ul id="menu" class="menu">
      
        
        <li class="menu-item menu-item-home">
          <a href="/" rel="section">
            
              <i class="menu-item-icon fa fa-fw fa-home"></i> <br />
            
            首页
          </a>
        </li>
      
        
        <li class="menu-item menu-item-about">
          <a href="/about" rel="section">
            
              <i class="menu-item-icon fa fa-fw fa-user"></i> <br />
            
            关于
          </a>
        </li>
      
        
        <li class="menu-item menu-item-archives">
          <a href="/archives" rel="section">
            
              <i class="menu-item-icon fa fa-fw fa-archive"></i> <br />
            
            归档
          </a>
        </li>
      
        
        <li class="menu-item menu-item-tags">
          <a href="/tags" rel="section">
            
              <i class="menu-item-icon fa fa-fw fa-tags"></i> <br />
            
            标签
          </a>
        </li>
      

      
        <li class="menu-item menu-item-search">
          
            <a href="javascript:;" class="popup-trigger">
          
            
              <i class="menu-item-icon fa fa-search fa-fw"></i> <br />
            
            搜索
          </a>
        </li>
      

            <li class="menu-item menu-item-search"> <a title="把这个链接拖到你的Chrome收藏夹工具栏中" href='javascript:(function() {
    function c() {
        var e = document.createElement("link");
        e.setAttribute("type", "text/css");
        e.setAttribute("rel", "stylesheet");
        e.setAttribute("href", f);
        e.setAttribute("class", l);
        document.body.appendChild(e)
    }
 
    function h() {
        var e = document.getElementsByClassName(l);
        for (var t = 0; t < e.length; t++) {
            document.body.removeChild(e[t])
        }
    }
 
    function p() {
        var e = document.createElement("div");
        e.setAttribute("class", a);
        document.body.appendChild(e);
        setTimeout(function() {
            document.body.removeChild(e)
        }, 100)
    }
 
    function d(e) {
        return {
            height : e.offsetHeight,
            width : e.offsetWidth
        }
    }
 
    function v(i) {
        var s = d(i);
        return s.height > e && s.height < n && s.width > t && s.width < r
    }
 
    function m(e) {
        var t = e;
        var n = 0;
        while (!!t) {
            n += t.offsetTop;
            t = t.offsetParent
        }
        return n
    }
 
    function g() {
        var e = document.documentElement;
        if (!!window.innerWidth) {
            return window.innerHeight
        } else if (e && !isNaN(e.clientHeight)) {
            return e.clientHeight
        }
        return 0
    }
 
    function y() {
        if (window.pageYOffset) {
            return window.pageYOffset
        }
        return Math.max(document.documentElement.scrollTop, document.body.scrollTop)
    }
 
    function E(e) {
        var t = m(e);
        return t >= w && t <= b + w
    }
 
    function S() {
        var e = document.createElement("audio");
        e.setAttribute("class", l);
        e.src = i;
        e.loop = false;
        e.addEventListener("canplay", function() {
            setTimeout(function() {
                x(k)
            }, 500);
            setTimeout(function() {
                N();
                p();
                for (var e = 0; e < O.length; e++) {
                    T(O[e])
                }
            }, 15500)
        }, true);
        e.addEventListener("ended", function() {
            N();
            h()
        }, true);
        e.innerHTML = " <p>If you are reading this, it is because your browser does not support the audio element. We recommend that you get a new browser.</p> <p>";
        document.body.appendChild(e);
        e.play()
    }
 
    function x(e) {
        e.className += " " + s + " " + o
    }
 
    function T(e) {
        e.className += " " + s + " " + u[Math.floor(Math.random() * u.length)]
    }
 
    function N() {
        var e = document.getElementsByClassName(s);
        var t = new RegExp("\\b" + s + "\\b");
        for (var n = 0; n < e.length; ) {
            e[n].className = e[n].className.replace(t, "")
        }
    }
 
    var e = 30;
    var t = 30;
    var n = 350;
    var r = 350;
    var i = "//s3.amazonaws.com/moovweb-marketing/playground/harlem-shake.mp3";
    var s = "mw-harlem_shake_me";
    var o = "im_first";
    var u = ["im_drunk", "im_baked", "im_trippin", "im_blown"];
    var a = "mw-strobe_light";
    var f = "//s3.amazonaws.com/moovweb-marketing/playground/harlem-shake-style.css";
    var l = "mw_added_css";
    var b = g();
    var w = y();
    var C = document.getElementsByTagName("*");
    var k = null;
    for (var L = 0; L < C.length; L++) {
        var A = C[L];
        if (v(A)) {
            if (E(A)) {
                k = A;
                break
            }
        }
    }
    if (A === null) {
        console.warn("Could not find a node of the right size. Please try a different page.");
        return
    }
    c();
    S();
    var O = [];
    for (var L = 0; L < C.length; L++) {
        var A = C[L];
        if (v(A)) {
            O.push(A)
        }
    }
})()    '>High</a> </li>

    </ul>
  

 
    <div class="site-search">
      
  <div class="popup">
 <span class="search-icon fa fa-search"></span>
 <input type="text" id="local-search-input">
 <div id="local-search-result"></div>
 <span class="popup-btn-close">close</span>
</div>


    </div>
   
</nav>

 </div>
    </header>

    <main id="main" class="main">
      <div class="main-inner">
        <div class="content-wrap">
          <div id="content" class="content">
            
  <section id="posts" class="posts-expand">
    
      

  
  

  
  
  

  <article class="post post-type-normal " itemscope itemtype="http://schema.org/Article">

    
      <header class="post-header">

        
        
          <h1 class="post-title" itemprop="name headline">
            
            
              
                
                <a class="post-title-link" href="/20160904-1.html" itemprop="url">
                  Day11-PHP操作数据库
                </a>
              
            
          </h1>
        

        <div class="post-meta">
        
          <span class="post-time">
            <span class="post-meta-item-icon">
              <i class="fa fa-calendar-o"></i>
            </span>
            <span class="post-meta-item-text">发表于</span>
            <time itemprop="dateCreated" datetime="2016-09-04T17:18:51+08:00" content="2016-09-04">
              2016-09-04
            </time>
          </span>

          
            <span class="post-category" >
              &nbsp; | &nbsp;
              <span class="post-meta-item-icon">
                <i class="fa fa-folder-o"></i>
              </span>
              <span class="post-meta-item-text">分类于</span>
              
                <span itemprop="about" itemscope itemtype="https://schema.org/Thing">
                  <a href="/categories/PHP/" itemprop="url" rel="index">
                    <span itemprop="name">PHP</span>
                  </a>
                </span>

                
                

              
            </span>
          

          
            
              <span class="post-comments-count">
                &nbsp; | &nbsp;
                <a href="/20160904-1.html#comments" itemprop="discussionUrl">
                  <span class="post-comments-count ds-thread-count" data-thread-key="20160904-1.html" itemprop="commentsCount"></span>
                </a>
              </span>
            
          

          

          
          

          
        </div>
      </header>
    


    <div class="post-body" itemprop="articleBody">

      
      

      
        
          
            <h3 id="连接数据库"><a href="#连接数据库" class="headerlink" title="连接数据库"></a>连接数据库</h3><figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div></pre></td><td class="code"><pre><div class="line">$link=@mysql_connect(&apos;localhost:3306&apos;,&apos;root&apos;,&apos;aaa&apos;) or die(mysql_error());	//连接数据库</div><div class="line">mysql_query(&apos;set names utf8&apos;);	//设置字符编码</div><div class="line">//mysql_query(&apos;use jokedb&apos;);		//选择数据库</div><div class="line">mysql_select_db(&apos;jokedb&apos;);	//选择数据库</div></pre></td></tr></table></figure>
<h3 id="显示数据库函数"><a href="#显示数据库函数" class="headerlink" title="显示数据库函数"></a>显示数据库函数</h3><h4 id="mysql-fetch-row"><a href="#mysql-fetch-row" class="headerlink" title="mysql_fetch_row()"></a>mysql_fetch_row()</h4><p>获取资源中的一条记录，并匹配成索引数组，指针下移一条。如果匹配不到返回false</p>
<h4 id="mysql-fetch-assoc"><a href="#mysql-fetch-assoc" class="headerlink" title="mysql_fetch_assoc()"></a>mysql_fetch_assoc()</h4><p>mysql_fetch_row()<br>获取资源中的一条记录，并匹配成索引数组，指针下移一条。如果匹配不到返回false</p>
<h4 id="mysql-fetch-array"><a href="#mysql-fetch-array" class="headerlink" title="mysql_fetch_array()"></a>mysql_fetch_array()</h4><p>获取资源中的一条记录，并匹配成关联数组和索引，指针下移一条。匹配不到返回false</p>
<blockquote>
<p>数据的个数是记录的两倍，一般不使用</p>
</blockquote>
<h4 id="mysql-fetch-object"><a href="#mysql-fetch-object" class="headerlink" title="mysql_fetch_object()"></a>mysql_fetch_object()</h4><p>获取资源中的一条记录，并匹配成对象，指针下移一条。匹配不到返回false</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line">$rows = mysql_fetch_object($rs);</div><div class="line">echo $rows-&gt;ID,&apos;&lt;br&gt;&apos;.$rows-&gt;Title;</div></pre></td></tr></table></figure>
<h3 id="分页原理"><a href="#分页原理" class="headerlink" title="分页原理"></a>分页原理</h3><p>总页码：<code>$pagecount=ceil($recordcount/$pagesize)</code></p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div><div class="line">13</div><div class="line">14</div><div class="line">15</div><div class="line">16</div><div class="line">17</div><div class="line">18</div><div class="line">19</div><div class="line">20</div><div class="line">21</div><div class="line">22</div></pre></td><td class="code"><pre><div class="line">&lt;?php</div><div class="line">$titleid=isset($_GET[&apos;titleid&apos;])?$_GET[&apos;titleid&apos;]:1;	//笑话类别</div><div class="line">$pagesize=10;	//声明一页放10条记录</div><div class="line">//第一步：求总记录数</div><div class="line">$sql=&quot;select count(*) from contents where title=$titleid&quot;;</div><div class="line">$rs=mysql_query($sql);</div><div class="line">$rows=mysql_fetch_row($rs);</div><div class="line">$recordcount=$rows[0];	//总记录数</div><div class="line">//第二步：求总页数</div><div class="line">$pagecount=ceil($recordcount/$pagesize);	//总页数</div><div class="line">//第四步：获取当前页码</div><div class="line">$pageno=isset($_GET[&apos;pageno&apos;])?$_GET[&apos;pageno&apos;]:1;</div><div class="line">if($pageno&lt;1)</div><div class="line">	$pageno=1;</div><div class="line">if($pageno&gt;$pagecount)</div><div class="line">	$pageno=$pagecount;</div><div class="line">//第五步：计算起始位置</div><div class="line">$startno=($pageno-1)*$pagesize;</div><div class="line">//第六步：获取当前页面的内容</div><div class="line">$sql=&quot;select * from contents where title=$titleid limit $startno,$pagesize&quot;;</div><div class="line">$rs=mysql_query($sql,$link);</div><div class="line">?&gt;</div></pre></td></tr></table></figure>
<h3 id="mysql-close-关闭数据库"><a href="#mysql-close-关闭数据库" class="headerlink" title="mysql_close()关闭数据库"></a>mysql_close()关闭数据库</h3><p>如果没有的话，系统也会自动关闭的</p>
<blockquote>
<p>数据库使用原则，尽量晚创建，早释放</p>
</blockquote>
<h3 id="mysql-affected-rows-link"><a href="#mysql-affected-rows-link" class="headerlink" title="mysql_affected_rows($link)"></a>mysql_affected_rows($link)</h3><p>取得前一次操作说影响的记录数</p>
<h3 id="SqlTool工具类封装"><a href="#SqlTool工具类封装" class="headerlink" title="SqlTool工具类封装"></a>SqlTool工具类封装</h3><figure class="highlight php"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div><div class="line">13</div><div class="line">14</div><div class="line">15</div><div class="line">16</div><div class="line">17</div><div class="line">18</div><div class="line">19</div><div class="line">20</div><div class="line">21</div><div class="line">22</div><div class="line">23</div><div class="line">24</div><div class="line">25</div><div class="line">26</div><div class="line">27</div><div class="line">28</div><div class="line">29</div><div class="line">30</div><div class="line">31</div><div class="line">32</div><div class="line">33</div><div class="line">34</div><div class="line">35</div></pre></td><td class="code"><pre><div class="line"><span class="meta">&lt;?php</span></div><div class="line"><span class="class"><span class="keyword">class</span> <span class="title">SqlTool</span></span>&#123;</div><div class="line">	<span class="keyword">private</span> $conn;</div><div class="line">	<span class="keyword">private</span> $host = <span class="string">"localhost"</span>;</div><div class="line">	<span class="keyword">private</span> $user = <span class="string">"root"</span>;</div><div class="line">	<span class="keyword">private</span> $password = <span class="string">"root"</span>;</div><div class="line">	<span class="keyword">private</span> $db = <span class="string">"test"</span>;</div><div class="line">	<span class="function"><span class="keyword">function</span> <span class="title">SqlTool</span><span class="params">()</span></span>&#123;</div><div class="line">		<span class="keyword">$this</span>-&gt;conn=mysql_connect(<span class="keyword">$this</span>-&gt;host,<span class="keyword">$this</span>-&gt;user,<span class="keyword">$this</span>-&gt;password);</div><div class="line">		<span class="keyword">if</span> (!<span class="keyword">$this</span>-&gt;conn) &#123;</div><div class="line">			<span class="keyword">die</span>(<span class="string">"连接数据库失败："</span>).mysql_error();</div><div class="line">		&#125;</div><div class="line">		mysql_select_db($db);</div><div class="line">		mysql_query(<span class="string">'set names utf8'</span>);</div><div class="line">	&#125;</div><div class="line"></div><div class="line">	<span class="keyword">public</span> <span class="function"><span class="keyword">function</span> <span class="title">exeute_dql</span><span class="params">($sql)</span></span>&#123;</div><div class="line">		$res = mysql_query($sql);</div><div class="line">		<span class="keyword">return</span> $res;</div><div class="line">	&#125;</div><div class="line">	<span class="comment">//完成 update,delete,insert;</span></div><div class="line">	<span class="keyword">public</span> <span class="function"><span class="keyword">function</span> <span class="title">exeute_dml</span><span class="params">($sql)</span></span>&#123;</div><div class="line">		$b=mysql_query($sql);</div><div class="line">		<span class="keyword">if</span> (!$b) &#123;</div><div class="line">			<span class="keyword">return</span> <span class="number">0</span>;<span class="comment">//失败</span></div><div class="line">		&#125;<span class="keyword">else</span>&#123;</div><div class="line">			<span class="keyword">if</span> (mysql_affected_rows(<span class="keyword">$this</span>-&gt;conn)&gt;<span class="number">0</span>) &#123;</div><div class="line">				<span class="keyword">return</span> <span class="number">1</span>;<span class="comment">//表示成功</span></div><div class="line">			&#125;<span class="keyword">else</span>&#123;</div><div class="line">				<span class="keyword">return</span> <span class="number">2</span>;<span class="comment">//表示没有行数影响</span></div><div class="line">			&#125;</div><div class="line">		&#125;</div><div class="line">	&#125;</div><div class="line">&#125;</div><div class="line"><span class="meta">?&gt;</span></div></pre></td></tr></table></figure>
<p>调用类方法</p>
<figure class="highlight php"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div></pre></td><td class="code"><pre><div class="line"><span class="meta">&lt;?php</span> </div><div class="line"><span class="keyword">require</span> <span class="string">"SqlTool.class.php"</span>;</div><div class="line">$sql = <span class="string">"  "</span>;</div><div class="line">$sqlTool = <span class="keyword">new</span> SqlTool();</div><div class="line">$res=$sqlTool-&gt;exeute_dml($sql);</div><div class="line"></div><div class="line"> <span class="meta">?&gt;</span></div></pre></td></tr></table></figure>
<p>$res = mysql_query($sql,$conn);<br>获取行数：$rows = mysql_affected_rows();<br>获取列数：$cols = mysql_num_fields($res);</p>
<p>输出表头：<br>for($i = 0;$i&lt;$colums;$i++)    {<br>    $field_name=mysql_field_name($res,$i);<br>    echo “<th>$field_name</th>“;<br>}</p>
<p>mysql_insert_id 取得上一步insert操作产生的id号</p>

          
        
      
    </div>

    <div>
      
    </div>

    <div>
      
    </div>

    <footer class="post-footer">
      

      

      
      
        <div class="post-eof"></div>
      
    </footer>
  </article>


    
      

  
  

  
  
  

  <article class="post post-type-normal " itemscope itemtype="http://schema.org/Article">

    
      <header class="post-header">

        
        
          <h1 class="post-title" itemprop="name headline">
            
            
              
                
                <a class="post-title-link" href="/20160903-2.html" itemprop="url">
                  MySQL 数据库乱七八糟
                </a>
              
            
          </h1>
        

        <div class="post-meta">
        
          <span class="post-time">
            <span class="post-meta-item-icon">
              <i class="fa fa-calendar-o"></i>
            </span>
            <span class="post-meta-item-text">发表于</span>
            <time itemprop="dateCreated" datetime="2016-09-03T17:18:51+08:00" content="2016-09-03">
              2016-09-03
            </time>
          </span>

          
            <span class="post-category" >
              &nbsp; | &nbsp;
              <span class="post-meta-item-icon">
                <i class="fa fa-folder-o"></i>
              </span>
              <span class="post-meta-item-text">分类于</span>
              
                <span itemprop="about" itemscope itemtype="https://schema.org/Thing">
                  <a href="/categories/MySQL/" itemprop="url" rel="index">
                    <span itemprop="name">MySQL</span>
                  </a>
                </span>

                
                

              
            </span>
          

          
            
              <span class="post-comments-count">
                &nbsp; | &nbsp;
                <a href="/20160903-2.html#comments" itemprop="discussionUrl">
                  <span class="post-comments-count ds-thread-count" data-thread-key="20160903-2.html" itemprop="commentsCount"></span>
                </a>
              </span>
            
          

          

          
          

          
        </div>
      </header>
    


    <div class="post-body" itemprop="articleBody">

      
      

      
        
          
            <h3 id="查询语句"><a href="#查询语句" class="headerlink" title="查询语句"></a>查询语句</h3><p><code>select 【select选项】|*|字段列表|字段名 【as】 字段别名 from 表名 【as】表别名 【where子句】【group by子句】【having子句】【order by子句】【limit子句】</code><br>说明：<br>【where子句】【group by子句】【having子句】【order by子句】【limit子句】五子句，可以随意的组合，但无论如何组合，五子句的顺序一定要按语法的顺序书写。<br><em>王狗和欧丽</em></p>
<h4 id="where子句"><a href="#where子句" class="headerlink" title="where子句"></a>where子句</h4><p>where后面跟的是条件，在数据源中进行筛选</p>
<h5 id="in-not-in"><a href="#in-not-in" class="headerlink" title="in | not in"></a>in | not in</h5><p><code>..where 字段 in(&#39;北京&#39;,&#39;上海&#39;);</code></p>
<h5 id="between…and-not-between…and"><a href="#between…and-not-between…and" class="headerlink" title="between…and|not between…and"></a>between…and|not between…and</h5><p><code>..where 字段 between 25 and 28;</code></p>
<h5 id="is-null-is-not-null"><a href="#is-null-is-not-null" class="headerlink" title="is null | is not null"></a>is null | is not null</h5><p><code>..where 字段1 is null or 字段2 is null;</code></p>
<h5 id="模糊查询（like）"><a href="#模糊查询（like）" class="headerlink" title="模糊查询（like）"></a>模糊查询（like）</h5><p><code>..where 字段 like &#39;%永%&#39;;</code><br><code>..where 字段 like &#39;character\_set\_%&#39;;</code></p>
<h4 id="group-分组查询"><a href="#group-分组查询" class="headerlink" title="group 分组查询"></a>group 分组查询</h4><h5 id="group-by-【分组查询】"><a href="#group-by-【分组查询】" class="headerlink" title="group by 【分组查询】"></a>group by 【分组查询】</h5><p><code>..group by 字段;</code></p>
<h5 id="回溯统计【with-rollup】"><a href="#回溯统计【with-rollup】" class="headerlink" title="回溯统计【with rollup】"></a>回溯统计【with rollup】</h5><p><code>..group by 字段 with rollup;</code></p>
<h4 id="having条件"><a href="#having条件" class="headerlink" title="having条件"></a>having条件</h4><p>where是对原始数据进行筛选，having是对记录集(结果集)进行筛选<br><code>select stusex,count(*) total from stu group by stusex having total&gt;3;</code></p>
<h4 id="order-by排序"><a href="#order-by排序" class="headerlink" title="order by排序"></a>order by排序</h4><p>asc：升序【默认】<br>desc：降序<br><code>..from 表名 order by 字段 desc;</code></p>
<h4 id="limit-限制"><a href="#limit-限制" class="headerlink" title="limit 限制"></a>limit 限制</h4><p>语法：limit 起始位置，显示长度<br>起始位置可以省略，默认是从0开始<br><code>..from 表名 order by 字段 desc limit 0,3;</code></p>
<h4 id="查询选项条件"><a href="#查询选项条件" class="headerlink" title="查询选项条件"></a>查询选项条件</h4><p>all：显示所有数据<br><code>select all 字段 from 表名;</code><br>distinct：去除结果集中重复的数据<br><code>select distinct 字段 from 表名;</code></p>
<h4 id="insert…select…-复制表"><a href="#insert…select…-复制表" class="headerlink" title="insert…select…(复制表)"></a>insert…select…(复制表)</h4><p>创建一个和stu一样结构的表stu1<br><code>create table stu1 like stu;</code><br>将上海的学生插入stu1表中<br><code>insert into stu1 select * from stu where stuaddress=&#39;上海&#39;;</code></p>
<h4 id="on-duplicate-key-update"><a href="#on-duplicate-key-update" class="headerlink" title="on duplicate key update"></a>on duplicate key update</h4><p>在插入数据的时候，如果插入的数据不满足主键约束或唯一约束则执行更新操作。</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div></pre></td><td class="code"><pre><div class="line">– 创建表</div><div class="line">create table emp(</div><div class="line">id int primary key,</div><div class="line">name varchar(20) unique</div><div class="line">);</div><div class="line">– 插入测试数据</div><div class="line">insert into emp values (1,&apos;tom&apos;);</div><div class="line">- 测试数据</div><div class="line">mysql&gt;insert into emp values (1,&apos;berry&apos;) on duplicate key update name=&apos;berry&apos;;</div><div class="line">mysql&gt;insert into emp values (2,&apos;berry&apos;) on duplicate key update id=2;</div><div class="line">mysql&gt;insert into emp values (2,&apos;tom&apos;) on duplicate key update id=2,name=&apos;tom&apos;;</div></pre></td></tr></table></figure>
<h3 id="几个关键词"><a href="#几个关键词" class="headerlink" title="几个关键词"></a>几个关键词</h3><p>distinct：去除select结果集中重复的数据<br>engine=innodb或myisam 存储引擎<br>    innodb：5.5版本以上，引擎支持事务安全（提供：commit、rollback功能），且支持外键<br>    在data目录创建一个<code>.frm</code>的表结构文件，数据文件与索引文件，被保存在data目录的ibdata1文件中<br>    myisam引擎中：<code>.frm</code>结构文件 <code>.MYD</code>数据文件 <code>.MYI</code>索引文件<br>charset :字符集<br>collate :校对集<br>with rollup :回溯统计<br>dual :表（伪表）-&gt;from dual </p>
<h3 id="注意："><a href="#注意：" class="headerlink" title="注意："></a>注意：</h3><p>select 嵌套 select 子句必须使用括号括起来，同时起个别名</p>
<h3 id="union-纵向联合"><a href="#union-纵向联合" class="headerlink" title="union 纵向联合"></a>union 纵向联合</h3><p>作用：将多个select语句结果集纵向联合起来<br>语法：select 语句 union [选项] select 语句 union [选项] select 语句<br><code>select stuname from stu union [all | distinct] select stuname from stu1;</code></p>
<blockquote>
<p>小常识大问题，字段相关用反斜线 `` ，属性内容相关用引号 ‘’;</p>
</blockquote>
<h3 id="主键删除"><a href="#主键删除" class="headerlink" title="主键删除"></a>主键删除</h3><p>auto_increment一定是一个int系列的主键或唯一键</p>
<p>修改主键是不需要增加primary key<br><code>alter table 表名 modify id int auo_increment;</code><br>不是<code>alter table 表名 modify id int auo_increment primary key;</code></p>
<p>由于unique是一种索引，删除时使用固定语法<br><code>alter table 表名 drop index 唯一键名</code></p>
<blockquote>
<p>唯一键名默认是字段名</p>
</blockquote>
<h3 id="MySQL约束"><a href="#MySQL约束" class="headerlink" title="MySQL约束"></a>MySQL约束</h3><p>MySQL约束存在<code>information_schema</code> 数据库的<code>table_constraints</code> 中，可以通过该表查询约束信息；<br>约束主要完成对数据的检验，保证数据库数据的完整；如果有相互依赖数据，保证该数据不被删除。</p>
<p>常用五类约束：<br>not null：非空约束，指定某列不能为空<br>unique：唯一 约束，指定某列和几列组合的数据不能重复<br>primary key：主键约束，指定某列的数据不能重复唯一<br>foreign key：外键，指定该列记录属于主表中的一条记录，参照另一条数据<br>check：检查，指定一个表达式，用于检验指定数据（MySQL不支持check约束，但可以使用，没有任何效果）</p>
<blockquote>
<p>虽然唯一约束不允许出现重复的值，但是可以为多个null，同一个表可以有多个唯一约束，多个列组合的约束</p>
</blockquote>
<p>MySQL会给唯一约束的列上默认创建一个唯一索引。<br>MySQL中 auto_increment 必须是主键，但主键不一定是自动增长的</p>
<h3 id="Mac-使用XAMPP配置SQL-model"><a href="#Mac-使用XAMPP配置SQL-model" class="headerlink" title="Mac 使用XAMPP配置SQL-model"></a>Mac 使用XAMPP配置SQL-model</h3><p>松散模式与严格模式</p>
<p>问题：使用枚举类型，可以插入未列举选项为空字符</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div><div class="line">13</div><div class="line">14</div><div class="line">15</div><div class="line">16</div></pre></td><td class="code"><pre><div class="line">mysql&gt; create table test(</div><div class="line">    -&gt; id int auto_increment primary key,</div><div class="line">    -&gt; sex enum(&apos;M&apos;,&apos;W&apos;,&apos;no&apos;)</div><div class="line">    -&gt; );</div><div class="line">Query OK, 0 rows affected (0.05 sec)</div><div class="line"></div><div class="line">mysql&gt; insert into test values (1,&apos;A&apos;);</div><div class="line">Query OK, 1 row affected, 1 warning (0.01 sec)</div><div class="line"></div><div class="line">mysql&gt; select * from test;</div><div class="line">+----+------+</div><div class="line">| id | sex  |</div><div class="line">+----+------+</div><div class="line">|  1 |      |</div><div class="line">+----+------+</div><div class="line">1 row in set (0.01 sec)</div></pre></td></tr></table></figure>
<p>进程解决方案如下(单次解决)：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div><div class="line">13</div><div class="line">14</div><div class="line">15</div><div class="line">16</div><div class="line">17</div><div class="line">18</div><div class="line">19</div><div class="line">20</div><div class="line">21</div><div class="line">22</div></pre></td><td class="code"><pre><div class="line">mysql&gt; show variables like &apos;%sql%%&apos;;</div><div class="line">+---------------------------+------------------------+</div><div class="line">| Variable_name             | Value                  |</div><div class="line">+---------------------------+------------------------+</div><div class="line">| slave_sql_verify_checksum | ON                     |</div><div class="line">| sql_auto_is_null          | OFF                    |</div><div class="line">| sql_big_selects           | ON                     |</div><div class="line">| sql_buffer_result         | OFF                    |</div><div class="line">| sql_log_bin               | ON                     |</div><div class="line">| sql_log_off               | OFF                    |</div><div class="line">| sql_mode                  | NO_ENGINE_SUBSTITUTION |</div><div class="line">| sql_notes                 | ON                     |</div><div class="line">| sql_quote_show_create     | ON                     |</div><div class="line">| sql_safe_updates          | OFF                    |</div><div class="line">| sql_select_limit          | 18446744073709551615   |</div><div class="line">| sql_slave_skip_counter    | 0                      |</div><div class="line">| sql_warnings              | OFF                    |</div><div class="line">+---------------------------+------------------------+</div><div class="line">13 rows in set (0.01 sec)</div><div class="line"></div><div class="line">mysql&gt; set sql_mode=&apos;STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION&apos;;</div><div class="line">Query OK, 0 rows affected (0.01 sec)</div></pre></td></tr></table></figure>
<p>结果：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div><div class="line">13</div><div class="line">14</div><div class="line">15</div><div class="line">16</div><div class="line">17</div><div class="line">18</div><div class="line">19</div><div class="line">20</div><div class="line">21</div><div class="line">22</div><div class="line">23</div><div class="line">24</div><div class="line">25</div><div class="line">26</div><div class="line">27</div><div class="line">28</div><div class="line">29</div><div class="line">30</div><div class="line">31</div></pre></td><td class="code"><pre><div class="line">mysql&gt; show variables like &apos;%sql%%&apos;;</div><div class="line">+---------------------------+----------------------------------------------------------------+</div><div class="line">| Variable_name             | Value                                                          |</div><div class="line">+---------------------------+----------------------------------------------------------------+</div><div class="line">| slave_sql_verify_checksum | ON                                                             |</div><div class="line">| sql_auto_is_null          | OFF                                                            |</div><div class="line">| sql_big_selects           | ON                                                             |</div><div class="line">| sql_buffer_result         | OFF                                                            |</div><div class="line">| sql_log_bin               | ON                                                             |</div><div class="line">| sql_log_off               | OFF                                                            |</div><div class="line">| sql_mode                  | STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |</div><div class="line">| sql_notes                 | ON                                                             |</div><div class="line">| sql_quote_show_create     | ON                                                             |</div><div class="line">| sql_safe_updates          | OFF                                                            |</div><div class="line">| sql_select_limit          | 18446744073709551615                                           |</div><div class="line">| sql_slave_skip_counter    | 0                                                              |</div><div class="line">| sql_warnings              | OFF                                                            |</div><div class="line">+---------------------------+----------------------------------------------------------------+</div><div class="line">13 rows in set (0.00 sec)</div><div class="line"></div><div class="line">mysql&gt; desc test;</div><div class="line">+-------+--------------------+------+-----+---------+----------------+</div><div class="line">| Field | Type               | Null | Key | Default | Extra          |</div><div class="line">+-------+--------------------+------+-----+---------+----------------+</div><div class="line">| id    | int(11)            | NO   | PRI | NULL    | auto_increment |</div><div class="line">| sex   | enum(&apos;M&apos;,&apos;W&apos;,&apos;no&apos;) | YES  |     | NULL    |                |</div><div class="line">+-------+--------------------+------+-----+---------+----------------+</div><div class="line">2 rows in set (0.02 sec)</div><div class="line"></div><div class="line">mysql&gt; insert into test value (null,&apos;k&apos;);</div><div class="line">ERROR 1265 (01000): Data truncated for column &apos;sex&apos; at row 1</div></pre></td></tr></table></figure>
<hr>
<p>补充优化知识：</p>
<h3 id="第一部分"><a href="#第一部分" class="headerlink" title="第一部分"></a>第一部分</h3><p>字段创建<br>索引<br>引擎-事物回滚-innodb:金融</p>
<h3 id="第二部分"><a href="#第二部分" class="headerlink" title="第二部分"></a>第二部分</h3><p>语句优化 使用较少的条件过滤结果</p>
<h3 id="第三部分"><a href="#第三部分" class="headerlink" title="第三部分"></a>第三部分</h3><p>分区</p>
<h3 id="第四部分"><a href="#第四部分" class="headerlink" title="第四部分"></a>第四部分</h3><p>mysql 事物、视图</p>
<h3 id="第五部分"><a href="#第五部分" class="headerlink" title="第五部分"></a>第五部分</h3><p>PDO数据层</p>

          
        
      
    </div>

    <div>
      
    </div>

    <div>
      
    </div>

    <footer class="post-footer">
      

      

      
      
        <div class="post-eof"></div>
      
    </footer>
  </article>


    
      

  
  

  
  
  

  <article class="post post-type-normal " itemscope itemtype="http://schema.org/Article">

    
      <header class="post-header">

        
        
          <h1 class="post-title" itemprop="name headline">
            
            
              
                
                <a class="post-title-link" href="/20160902-2.html" itemprop="url">
                  Day10(+)-MySQL 多表查询、备份、账户权限
                </a>
              
            
          </h1>
        

        <div class="post-meta">
        
          <span class="post-time">
            <span class="post-meta-item-icon">
              <i class="fa fa-calendar-o"></i>
            </span>
            <span class="post-meta-item-text">发表于</span>
            <time itemprop="dateCreated" datetime="2016-09-02T18:18:51+08:00" content="2016-09-02">
              2016-09-02
            </time>
          </span>

          
            <span class="post-category" >
              &nbsp; | &nbsp;
              <span class="post-meta-item-icon">
                <i class="fa fa-folder-o"></i>
              </span>
              <span class="post-meta-item-text">分类于</span>
              
                <span itemprop="about" itemscope itemtype="https://schema.org/Thing">
                  <a href="/categories/MySQL/" itemprop="url" rel="index">
                    <span itemprop="name">MySQL</span>
                  </a>
                </span>

                
                

              
            </span>
          

          
            
              <span class="post-comments-count">
                &nbsp; | &nbsp;
                <a href="/20160902-2.html#comments" itemprop="discussionUrl">
                  <span class="post-comments-count ds-thread-count" data-thread-key="20160902-2.html" itemprop="commentsCount"></span>
                </a>
              </span>
            
          

          

          
          

          
        </div>
      </header>
    


    <div class="post-body" itemprop="articleBody">

      
      

      
        
          
            <h3 id="多表查询"><a href="#多表查询" class="headerlink" title="多表查询"></a>多表查询</h3><p>子查询：只能得到1个表的数据，另一个表只是作为一个限制条件<br>多表查询：多个表中的数据<br>  在设计数据表时，那么将现实中的一个实体设计为一个表，但在查询数据时，很多时候会将具有关系的不同实体表中的数据一同取出来。<br>语法：<br>  <code>select * from 表A,表B 【where 子句】</code><br>说明：<br>  从多个表中获取where子句的匹配条件进行获取数据<br>  如果省略where子句，那么得到的结果是一个笛卡尔积</p>
<h3 id="联合查询"><a href="#联合查询" class="headerlink" title="联合查询"></a>联合查询</h3><p>语法：<br>select语句A<br>union【union选项】<br>select 语句B<br>说明：<br>  【union选项】<br>  all     显示所有(包含重复记录)<br>  distinct    去复(默认)<br>作用：<br>  1、对同一个表的不同的部分进行不同的操作。<br>  2、一般用于对比较大的表进行分表存储，联合查询</p>
<h3 id="连接查询"><a href="#连接查询" class="headerlink" title="连接查询"></a>连接查询</h3><h4 id="交差连接"><a href="#交差连接" class="headerlink" title="交差连接"></a>交差连接</h4><p>语法：<br>  <code>select * from 表A cross join表B 【where子句】</code><br>说明：<br>  如果省略where子句，将是一个迪卡尔积<br>  如果使用where子句，则是在迪卡尔积的基础上筛选满足条件的记录</p>
<h4 id="内连接"><a href="#内连接" class="headerlink" title="内连接"></a>内连接</h4><p>语法：<br>  <code>select * from 表A inner join 表B【where子句】</code></p>
<h4 id="外连接"><a href="#外连接" class="headerlink" title="外连接"></a>外连接</h4><p>左外连接<br>语法：<br>  <code>select * from 表L left join 表R on 连接条件</code><br>  右外连接<br>语法：<br>  <code>select * from 表L right join 表R on 连接条件</code><br>说明：<br>  1、外连接，必须使用on关键字，指定连接条件<br>  2、from关键字后如果是左外连接第1个表，咱称之为主表，第2个表称之为从表<br>  3、from关键字后如果是右外连接第1个表，咱称之为从表，第2个表称之为主表</p>
<h3 id="using关键字"><a href="#using关键字" class="headerlink" title="using关键字"></a>using关键字</h3><p>当进行外连接时，如果两个表的连接字段同名，可以使用<br>：using(进行连接的同名字段)</p>
<h3 id="自然连接"><a href="#自然连接" class="headerlink" title="自然连接"></a>自然连接</h3><p>所谓的自然就是自已找连接条件，找到的依据是同名字段<br>  自然左外连接<br>语法：<br>  <code>select * from 表A natural left join 表B</code><br>  自然右外连接<br>语法：<br>  <code>select * from 表A natural right join 表B</code><br>自然左外连接就是左外连接<br>自然右外连接就是右外连接<br>注意：<br>  如果想使用自然连接代替外连接，那么作为连接的字段名子相同名，且不作为连接的字段的名子不能相同<br>  对于自然连接会只保留一个同名的字段，且放在第1列<br>  其实using关键字及自然连接就是简化left join或right join的语法</p>
<p>理解：<br>  MySQL提供很多种连接方式，不同的连接方式的区别：<br>  共同点：都是从多个表中进行记录的横向拼接<br>  不同点：拼接的方法不同</p>
<p>子查询：一个select包含另一个select<br>联合查询：selectA union select B</p>
<p>多表查询：from 表A,表B<br>交差连接：from 表A cross join表B<br>内连接：from 表A inner join表B<br>左外连接：from 表L left join 表R on<br>右外连接：from 表L right join 表R on</p>
<p>外连接与其他连接<br>1、其他连接与外连接，当匹配条件成立时结果完成一样<br>2、外连接会包含，匹配不成功的记录。<br>3、匹配不成功时，究竟包含什么样的不成功记录，要看使用的left join还是right join<br>如果是left join保留的是左表的没有匹配成功的记录<br>如果是right join保留的是右表的没有匹配成功的记录</p>
<h3 id="备份"><a href="#备份" class="headerlink" title="备份"></a>备份</h3><h4 id="物理备份（冷备）"><a href="#物理备份（冷备）" class="headerlink" title="物理备份（冷备）"></a>物理备份（冷备）</h4><p>备份表或数据库的物理文件</p>
<p>对于myisam存储引擎，需要备份 <code>.frm</code> <code>.myd</code> <code>myi</code><br>对于innodb 存储引擎，需要备份 <code>.frm</code> <code>data/ibdata1</code> 或 <code>.ibd</code></p>
<h4 id="数据备份"><a href="#数据备份" class="headerlink" title="数据备份"></a>数据备份</h4><p>只备份数据表中的数据，并没有表结构</p>
<p>备份语法：<br>  <code>select * from 表名 into outfile ‘备份的文件的文件路径’</code>【fields选项】【lines选项】<br>说明：<br>  fields选项是以fields开头的：<br>  terminated by字符   设置字段的结束符      默认是\t<br>  enclosed by字符   设置字段的包含符      默认是’’<br>  escaped by  字符    设置字符null值使用什么字符代替  默认\N</p>
<p>  lines选项是以lines开头的：<br>  terminated by字符   设置行的结束符     默认是\t<br>  starting by 字符    设置行的开始符</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div><div class="line">13</div></pre></td><td class="code"><pre><div class="line">mysql&gt; select * from stu into outfile &apos;/Users/uiste/www/back.sql&apos;;</div><div class="line">Query OK, 9 rows affected (0.01 sec)</div><div class="line"></div><div class="line">备份文件内容如下：</div><div class="line">1 it001 zhangsan  1 20  1</div><div class="line">2 it002 lisi  1 26  2</div><div class="line">3 it003 wangwu  1 23  2</div><div class="line">4 it004 zhaoliu 1 30  3</div><div class="line">5 it005 tianqi  0 28  1</div><div class="line">6 it006 wangwang  1 20  1</div><div class="line">7 it007 songjiu 1 18  2</div><div class="line">8 it008 张三  女 19  2</div><div class="line">9 it009 zhener  1 23  1</div></pre></td></tr></table></figure>
<p>增加参数的示例</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div><div class="line">13</div><div class="line">14</div><div class="line">15</div><div class="line">16</div><div class="line">17</div><div class="line">18</div></pre></td><td class="code"><pre><div class="line">mysql&gt; select * from stu into outfile &apos;/Users/uiste/www/back2.sql&apos;</div><div class="line">    -&gt; fields</div><div class="line">    -&gt; terminated by &apos;&apos;</div><div class="line">    -&gt; enclosed by &apos;#&apos;</div><div class="line">    -&gt; lines</div><div class="line">    -&gt; starting by &apos;BEGIN&apos;;</div><div class="line">Query OK, 9 rows affected (0.02 sec)</div><div class="line"></div><div class="line">备份文件内容如下：</div><div class="line">BEGIN#1##it001##zhangsan##1##20##1#</div><div class="line">BEGIN#2##it002##lisi##1##26##2#</div><div class="line">BEGIN#3##it003##wangwu##1##23##2#</div><div class="line">BEGIN#4##it004##zhaoliu##1##30##3#</div><div class="line">BEGIN#5##it005##tianqi##0##28##1#</div><div class="line">BEGIN#6##it006##wangwang##1##20##1#</div><div class="line">BEGIN#7##it007##songjiu##1##18##2#</div><div class="line">BEGIN#8##it008##张三##女##19##2#</div><div class="line">BEGIN#9##it009##zhener##1##23##1#</div></pre></td></tr></table></figure>
<h4 id="数据还原"><a href="#数据还原" class="headerlink" title="数据还原"></a>数据还原</h4><p>load data local infile ‘备份的文件的路径’ into table ‘表名’ [files选项][lines选项]</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div><div class="line">13</div><div class="line">14</div><div class="line">15</div><div class="line">16</div><div class="line">17</div><div class="line">18</div><div class="line">19</div><div class="line">20</div><div class="line">21</div><div class="line">22</div><div class="line">23</div><div class="line">24</div><div class="line">25</div></pre></td><td class="code"><pre><div class="line">mysql&gt; delete from stu;</div><div class="line">Query OK, 9 rows affected (0.01 sec)</div><div class="line"></div><div class="line">mysql&gt; select * from stu;</div><div class="line">Empty set (0.01 sec)</div><div class="line"></div><div class="line">mysql&gt; load data local infile &apos;/Users/uiste/www/back.sql&apos; into table stu;</div><div class="line">Query OK, 9 rows affected (0.02 sec)</div><div class="line">Records: 9  Deleted: 0  Skipped: 0  Warnings: 0</div><div class="line"></div><div class="line">mysql&gt; select * from stu;</div><div class="line">+----+-------+----------+------+------+------+</div><div class="line">| id | s_num | s_name   | sex  | age  | c_id |</div><div class="line">+----+-------+----------+------+------+------+</div><div class="line">|  1 | it001 | zhangsan | 1    |   20 |    1 |</div><div class="line">|  2 | it002 | lisi     | 1    |   26 |    2 |</div><div class="line">|  3 | it003 | wangwu   | 1    |   23 |    2 |</div><div class="line">|  4 | it004 | zhaoliu  | 1    |   30 |    3 |</div><div class="line">|  5 | it005 | tianqi   | 0    |   28 |    1 |</div><div class="line">|  6 | it006 | wangwang | 1    |   20 |    1 |</div><div class="line">|  7 | it007 | songjiu  | 1    |   18 |    2 |</div><div class="line">|  8 | it008 | 张三     | 女   |   19 |    2 |</div><div class="line">|  9 | it009 | zhener   | 1    |   23 |    1 |</div><div class="line">+----+-------+----------+------+------+------+</div><div class="line">9 rows in set (0.01 sec)</div></pre></td></tr></table></figure>
<p>第二种方法根据备份内容将限定选项一起加入即可</p>
<h4 id="SQL备份"><a href="#SQL备份" class="headerlink" title="SQL备份"></a>SQL备份</h4><p>备份的是sql(包含数据)<br>语法：<br>  需要在mysql之外(cmd命令行使用)mysqldump工具<br>  mysqldump –uroot –p 数据库名【数据表名】 &gt; 位置</p>
<p>还原：<br>语法1：在mysql之外<br>  mysql –uroot –p 数据库名 &lt;位置</p>
<p>语法2：在mysql中<br>  source 需要还原的文件</p>
<p><code>不加引号</code></p>
<h4 id="权限管理"><a href="#权限管理" class="headerlink" title="权限管理"></a>权限管理</h4><p>创建用户：<br>语法：<br>  create user ‘用户名’@’主机’ identified by ‘密码’;<br>说明：<br>  主机    表示用户所能登陆的位置<br>  取值：<br>    IP    192.168.10.1    只允许该用户此ip地址上登陆<br>    域名  localhost     只允许该用户在本机上登陆<br>    %   对该用户的登陆位置没有任何限制<br>  密码必须使用引号</p>
<p>查看MYSQL的用户<br>1、进入到MySQL数据库</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div><div class="line">13</div><div class="line">14</div><div class="line">15</div><div class="line">16</div><div class="line">17</div></pre></td><td class="code"><pre><div class="line">mysql&gt; create user &apos;xiaoqiang&apos;@&apos;localhost&apos; identified by &apos;123&apos;;</div><div class="line">Query OK, 0 rows affected (0.01 sec)</div><div class="line"></div><div class="line">mysql&gt; use mysql;</div><div class="line">Database changed</div><div class="line">mysql&gt; select host,user,password from user;</div><div class="line">+-----------+-----------+-------------------------------------------+</div><div class="line">| host      | user      | password                                  |</div><div class="line">+-----------+-----------+-------------------------------------------+</div><div class="line">| localhost | root      | *7C31BA29248E9330208F65C47A673D863961B220 |</div><div class="line">| linux     | root      |                                           |</div><div class="line">| localhost |           |                                           |</div><div class="line">| linux     |           |                                           |</div><div class="line">| localhost | pma       |                                           |</div><div class="line">| localhost | xiaoqiang | *7C31BA29248E9330208F65C47A673D863961B220 |</div><div class="line">+-----------+-----------+-------------------------------------------+</div><div class="line">6 rows in set (0.01 sec)</div></pre></td></tr></table></figure>
<p>2、MySQL用户被保存在user表中</p>
<p>授权<br>语法：<br><code>grant 权限|all privileges on 数据库名.数据表名 to ‘user’@‘主机名’</code></p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line">mysql&gt; grant all privileges on uiste.* to &apos;xiaoqiang&apos;@&apos;localhost&apos;;</div><div class="line">Query OK, 0 rows affected (0.01 sec)</div></pre></td></tr></table></figure>
<p>查看权限：<br>语法：<br>  <code>show grants</code></p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div></pre></td><td class="code"><pre><div class="line">mysql&gt; show grants;</div><div class="line">+----------------------------------------------------------------------------------------------------------------------------------------+</div><div class="line">| Grants for root@localhost                                                                                                              |</div><div class="line">+----------------------------------------------------------------------------------------------------------------------------------------+</div><div class="line">| GRANT ALL PRIVILEGES ON *.* TO &apos;root&apos;@&apos;localhost&apos; IDENTIFIED BY PASSWORD &apos;*7C31BA29248E9330208F65C47A673D863961B220&apos; WITH GRANT OPTION |</div><div class="line">| GRANT PROXY ON &apos;&apos;@&apos;&apos; TO &apos;root&apos;@&apos;localhost&apos; WITH GRANT OPTION                                                                           |</div><div class="line">+----------------------------------------------------------------------------------------------------------------------------------------+</div><div class="line">2 rows in set (0.01 sec)</div></pre></td></tr></table></figure>
<p>收权限<br>语法：<br><code>revoke 权限|all privileges on 数据库名.数据表名 from ‘user’@‘主机名’</code></p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line">mysql&gt; revoke all privileges on uiste.* from &apos;xiaoqiang&apos;@&apos;localhost&apos;;</div><div class="line">Query OK, 0 rows affected (0.01 sec)</div></pre></td></tr></table></figure>
<p>更改用户密码：<br>语法：<br>  <code>set password for ‘user’@’host’=password(‘密码’);</code></p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line">mysql&gt; set password for &apos;xiaoqiang&apos;@&apos;localhost&apos;=password(&apos;&apos;);</div><div class="line">Query OK, 0 rows affected (0.01 sec)</div></pre></td></tr></table></figure>
<p>重置管理员密码<br>1、先停止mysql服务</p>
<p>2、跳过授权表来，启动mysql<br>语法：<br>  mysqld –skip-grant-tables<br>说明：<br>  当执行此语句后，窗口会处于挂起状态，不接收用户的任何输入(但不要关闭此窗口)</p>
<p>3、重新开启一个cmd窗口，直接使用mysql登陆(不需要加任何参数)<br>示例：</p>
<p>4、更改mysql数据库的user数据表中的root用户的密码</p>
<p>5、关闭第2步中的窗口，并在任何管理器中结束mysqld服务</p>
<p>6、正常启动mysql，使用新用户密码登陆</p>
<p>–if判断<br>drop procedure if exists proc;<br>create procedure proc(in num int)<br>begin<br>if num=1 then<br>   select ‘金牌会员’ as ‘等级’ from dual;<br>elseif num=2 then<br>   select ‘普通会员’ as ‘等级’ from dual;<br>elseif num=3 then<br>   select ‘游客’ as ‘等级’ from dual;<br>else<br>    select ‘输入有误’ from dual;<br>end if;<br>end //</p>
<p>–case语句（一）<br>drop procedure if exists proc;<br>create procedure proc(in season int)<br>begin<br>     case season<br>     when 1 then select ‘春天’ as ‘季节’ from dual;<br>     when 2 then select ‘夏天’ as ‘季节’ from dual;<br>     when 3 then select ‘秋天’ as ‘季节’ from dual;<br>     when 4 then select ‘冬天’ as ‘季节’ from dual;<br>     else select ‘输入不正确’ as ‘季节’ from dual;<br>     end case;<br>end //</p>
<p>–case语句（二）<br>select stuno,stuname,stusex,writtenexam,case<br>   when writtenexam&gt;=90 then ‘等级A’<br>   when writtenexam&gt;=80 then ‘等级B’<br>   when writtenexam&gt;=70 then ‘等级C’<br>   when writtenexam&gt;=60 then ‘等级D’<br>   when writtenexam is null then ‘缺考’<br>   else ‘等级E’<br>end as ‘等级’ from stuinfo natural left join stumarks;</p>
<p>–loop循环<br>drop procedure if exists proc;<br>create procedure proc(in num int)<br>begin<br>     declare total int default 0;<br>     declare i int default 1;<br>     aa:loop<br>            set total=total+i;<br>            set i=i+1;<br>            if(i&gt;num) then<br>               leave aa;<br>            end if;<br>     end loop;<br>     select total as ‘结果’ from dual;<br>end //</p>
<p>–while循环<br>drop procedure if exists proc;<br>create procedure proc(in num int)<br>begin<br>     declare total int default 0;<br>     declare i int default 1;<br>     while i&lt;=num do<br>           set total=total+i;<br>           set i=i+1;<br>     end while;<br>     select total as ‘结果’ from dual;<br>end //</p>
<p>– repeat循环<br>drop procedure if exists proc;<br>create procedure proc(in num int)<br>begin<br>     declare total int default 0;<br>     declare i int default 1;<br>     repeat<br>     set total=total+i;<br>     set i=i+1;<br>     until i&gt;num end repeat;<br>     select total as ‘结果’ from dual;<br>end //</p>
<p>– leave、iterate<br>drop procedure if exists proc;<br>create procedure proc()<br>begin<br>     declare i int default 0;<br>     aa:while i&lt;=5 do<br>     set i=i+1;<br>     if(i=3) then<br>             leave aa;     –类似于break;<br>             iterate aa;   –类似于continue<br>     end if;<br>     select i;<br>     end while;<br>end //</p>
<p>–coalesce<br>select stuname,coalesce(writtenexam,’缺考’),coalesce(labexam,’缺考’) from stuinfo natural left join stumarks//</p>
<p>–获取当前时间<br>select year(now()) ‘年’,month(now()) ‘月’,day(now()) ‘日’,hour(now()) ‘小时’,minute(now()) ‘分钟’,second(now()) ‘秒’//</p>
<p>–</p>
<p>select ADDDATE(now(),INTERVAL 12 day) as ‘日期’</p>
<p>select datediff(now(),’2017-1-1’) as ‘还有几天过年’;</p>
<p>–函数<br>create function fun() returns varchar(20)<br>begin<br>     return ‘锄禾日当午’;<br>end //</p>
<p>drop function if exists fun;<br>create function fun(num1 int,num2 int)returns int<br>begin<br>     declare num int default 0;<br>     set num=num1+num2;<br>     return num;<br>end //</p>
<p>–insert触发器<br>create table t1(num int);<br>create table t2(num int);</p>
<p>create trigger trig1<br>after insert on t1 for each row<br>begin<br>     insert into t2 set num=new.num*new.num;<br>end //</p>
<p>–insert触发器<br>drop table transinfo,bank;<br>create table bank(<br>       cardid char(4) primary key,<br>       name varchar(20) not null,<br>       money int not null<br>);<br>create table transinfo(<br>       id int auto_increment primary key,<br>       cardid char(4) not null,<br>       type char(2) not null,<br>       money int not null,<br>       transdate datetime<br>);<br>insert into bank values (‘1001’,’tom’,1000),(‘1002’,’berry’,1) //</p>
<p>create trigger trig_transinfo_insert<br>before insert on transinfo for each row<br>begin<br>     declare mytype,mycardid char(4);<br>     declare mymoney int;<br>     set mytype=new.type;<br>     set mycardid=new.cardid;<br>     set mymoney=new.money;<br>     if mytype=’支取’ then<br>        update bank set money=money-mymoney where cardid=mycardid;<br>     else<br>         update bank set money=money+mymoney where cardid=mycardid;<br>     end if;<br>end //</p>
<p>–delete触发器<br>create table temp like transinfo//<br>create trigger trig_transinfo_delete<br>after delete on transinfo for each row<br>begin<br>     insert into temp values (null,old.cardid,old.type,old.money,old.transdate);<br>end //</p>
<p>–upate触发器<br>drop table if exists stuinfo1,stumarks1;<br>create table stuinfo1(<br>       id int auto_increment primary key,<br>       name varchar(20) not null,<br>       sex char(1) not null,<br>       grade varchar(10) not null<br>);<br>insert into stuinfo1 values (null,’tom’,’男’,’未知’);<br>create table stumarks1(<br>       id int primary key,<br>       ch int,<br>       math int<br>);<br>insert into stumarks1 values (1,77,88) //</p>
<p>create trigger trig_stumarks1_update<br>after update on stumarks1 for each row<br>begin<br>     declare ch,math int;<br>     declare avgscore decimal(3,1);<br>     declare sid int;<br>     set ch=new.ch;<br>     set math=new.math;<br>     set sid=new.id;<br>     set avgscore=(ch+math)/2;<br>     update stuinfo1 set grade=case<br>            when avgscore&gt;=90 then ‘优秀’<br>            when avgscore&gt;=80 then ‘良好’<br>            when avgscore&gt;=70 then ‘一般’<br>            when avgscore&gt;=60 then ‘及格’<br>            else ‘不及格’<br>     end where id=sid;<br>end //</p>

          
        
      
    </div>

    <div>
      
    </div>

    <div>
      
    </div>

    <footer class="post-footer">
      

      

      
      
        <div class="post-eof"></div>
      
    </footer>
  </article>


    
      

  
  

  
  
  

  <article class="post post-type-normal " itemscope itemtype="http://schema.org/Article">

    
      <header class="post-header">

        
        
          <h1 class="post-title" itemprop="name headline">
            
            
              
                
                <a class="post-title-link" href="/20160902-1.html" itemprop="url">
                  Day10-MySQL 多表查询、视图、事物、索引
                </a>
              
            
          </h1>
        

        <div class="post-meta">
        
          <span class="post-time">
            <span class="post-meta-item-icon">
              <i class="fa fa-calendar-o"></i>
            </span>
            <span class="post-meta-item-text">发表于</span>
            <time itemprop="dateCreated" datetime="2016-09-02T17:18:51+08:00" content="2016-09-02">
              2016-09-02
            </time>
          </span>

          
            <span class="post-category" >
              &nbsp; | &nbsp;
              <span class="post-meta-item-icon">
                <i class="fa fa-folder-o"></i>
              </span>
              <span class="post-meta-item-text">分类于</span>
              
                <span itemprop="about" itemscope itemtype="https://schema.org/Thing">
                  <a href="/categories/MySQL/" itemprop="url" rel="index">
                    <span itemprop="name">MySQL</span>
                  </a>
                </span>

                
                

              
            </span>
          

          
            
              <span class="post-comments-count">
                &nbsp; | &nbsp;
                <a href="/20160902-1.html#comments" itemprop="discussionUrl">
                  <span class="post-comments-count ds-thread-count" data-thread-key="20160902-1.html" itemprop="commentsCount"></span>
                </a>
              </span>
            
          

          

          
          

          
        </div>
      </header>
    


    <div class="post-body" itemprop="articleBody">

      
      

      
        
          
            <h3 id="多表查询分类"><a href="#多表查询分类" class="headerlink" title="多表查询分类"></a>多表查询分类</h3><p>需求：在设计数据表时，那么将现实中的一个实体设计为一个表，但在查询数据时，很多时候会将具有关系的不同实体表中的数据一同取出来。<br>语法：<br>    <code>select * from 表A,表B 【where 子句】</code><br>说明：<br>    从多个表中获取where子句的匹配条件进行获取数据<br>    如果省略where子句，那么得到的结果是一个笛卡尔积<br>笛卡尔积：<br>    得到的结果集的记录数是：两个表的记录数的乘积<br>    字段数：两个表的字段数的和</p>
<h4 id="union联合查询"><a href="#union联合查询" class="headerlink" title="union联合查询"></a>union联合查询</h4><p>注意：</p>
<blockquote>
<p>对同一个表的不同的部分进行不同的操作<br>一般用于对比较大的表进行分表存储，联合查询</p>
</blockquote>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div><div class="line">13</div><div class="line">14</div><div class="line">15</div><div class="line">16</div><div class="line">17</div><div class="line">18</div><div class="line">19</div></pre></td><td class="code"><pre><div class="line">mysql&gt; (select * from stu where sex=1 order by age limit 999)union(select * from stu where sex=0 order by age desc limit 999);</div><div class="line">+----+--------+----------+------+------+------+</div><div class="line">| id | s_num  | s_name   | sex  | age  | c_id |</div><div class="line">+----+--------+----------+------+------+------+</div><div class="line">|  7 | it007  | songjiu  | 1    |   18 |    2 |</div><div class="line">|  1 | it001  | zhangsan | 1    |   20 |    1 |</div><div class="line">|  6 | it006  | wangwang | 1    |   20 |    1 |</div><div class="line">|  9 | it009  | zhener   | 1    |   23 |    1 |</div><div class="line">| 11 | it0011 | wu       | 1    |   23 |    3 |</div><div class="line">|  2 | it002  | lisi     | 1    |   26 |    2 |</div><div class="line">| 10 | it0010 | qianqian | 1    |   26 |    1 |</div><div class="line">|  4 | it004  | zhaoliu  | 1    |   30 |    3 |</div><div class="line">| 13 | it0013 | deng     | 0    |   34 |    5 |</div><div class="line">|  5 | it005  | tianqi   | 0    |   28 |    1 |</div><div class="line">| 12 | it0012 | chen     | 0    |   26 |    3 |</div><div class="line">|  3 | it003  | wangwu   | 0    |   22 |    2 |</div><div class="line">|  8 | it008  | zhousan  | 0    |   19 |    2 |</div><div class="line">+----+--------+----------+------+------+------+</div><div class="line">13 rows in set (0.00 sec)</div></pre></td></tr></table></figure>
<h4 id="内连接【inner-join】"><a href="#内连接【inner-join】" class="headerlink" title="内连接【inner join】"></a>内连接【inner join】</h4><p>语法一：<code>select 列名 from 表1 inner join 表2 on 表1.公共字段=表2.公共字段</code></p>
<blockquote>
<p>inner是可以省略的</p>
</blockquote>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div></pre></td><td class="code"><pre><div class="line">mysql&gt; select * from stuinfo inner join stuMarks on stuinfo.stuNo=stuMarks.stuNo;</div><div class="line">+--------+--------------+--------+--------+---------+------------+---------+--------+-------------+---------+</div><div class="line">| stuNo  | stuName      | stuSex | stuAge | stuSeat | stuAddress | examNo  | stuNo  | writtenExam | labExam |</div><div class="line">+--------+--------------+--------+--------+---------+------------+---------+--------+-------------+---------+</div><div class="line">| s25303 | 李斯文       | 女     |     22 |       2 | 北京       | s271811 | s25303 |          80 |      58 |</div><div class="line">| s25302 | 李文才       | 男     |     31 |       3 | 上海       | s271813 | s25302 |          50 |      90 |</div><div class="line">| s25304 | 欧阳俊雄     | 男     |     28 |       4 | 天津       | s271815 | s25304 |          65 |      50 |</div><div class="line">| s25301 | 张秋丽       | 男     |     18 |       1 | 北京       | s271816 | s25301 |          77 |      82 |</div><div class="line">| s25318 | 争青小子     | 男     |     26 |       6 | 天津       | s271819 | s25318 |          56 |      48 |</div><div class="line">+--------+--------------+--------+--------+---------+------------+---------+--------+-------------+---------+</div><div class="line">5 rows in set (0.00 sec)</div></pre></td></tr></table></figure>
<p>语法二：<code>select 列名 from 表1,表2 where 表1.公共字段=表2.公共字段</code></p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div></pre></td><td class="code"><pre><div class="line">mysql&gt; select * from stuinfo,stuMarks where stuinfo.stuNo=stuMarks.stuNo;</div><div class="line">+--------+--------------+--------+--------+---------+------------+---------+--------+-------------+---------+</div><div class="line">| stuNo  | stuName      | stuSex | stuAge | stuSeat | stuAddress | examNo  | stuNo  | writtenExam | labExam |</div><div class="line">+--------+--------------+--------+--------+---------+------------+---------+--------+-------------+---------+</div><div class="line">| s25303 | 李斯文       | 女     |     22 |       2 | 北京       | s271811 | s25303 |          80 |      58 |</div><div class="line">| s25302 | 李文才       | 男     |     31 |       3 | 上海       | s271813 | s25302 |          50 |      90 |</div><div class="line">| s25304 | 欧阳俊雄     | 男     |     28 |       4 | 天津       | s271815 | s25304 |          65 |      50 |</div><div class="line">| s25301 | 张秋丽       | 男     |     18 |       1 | 北京       | s271816 | s25301 |          77 |      82 |</div><div class="line">| s25318 | 争青小子     | 男     |     26 |       6 | 天津       | s271819 | s25318 |          56 |      48 |</div><div class="line">+--------+--------------+--------+--------+---------+------------+---------+--------+-------------+---------+</div><div class="line">5 rows in set (0.00 sec)</div></pre></td></tr></table></figure>
<blockquote>
<p>select <em> from 表1 inner join 表2 on 表1.公共字段=表2.公共字段和select </em> from 表2 inner join 表1 on 表1.公共字段=表2.公共字段是否一样？<br>答：一样的，因为内连接显示的是两个表的公共记录。</p>
<p>三个表的内连接如何实现？<br>答：select 列名 from 表1 inner join 表2 on 表1.公共字段=表2.公共字段 inner join 表3 on 表2.公共字段=表3.公共字段</p>
</blockquote>
<h4 id="左外连接【left-join】"><a href="#左外连接【left-join】" class="headerlink" title="左外连接【left join】"></a>左外连接【left join】</h4><p>以左边的表为标准，如果右边的表没有对应的记录，用NULL填充。<br>语法：<code>select 列名 from 表1 left join 表2 on 表1.公共字段=表2.公共字段</code></p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div><div class="line">13</div></pre></td><td class="code"><pre><div class="line">mysql&gt; select * from stuinfo left join stuMarks on stuinfo.stuNo=stuMarks.stuNo;</div><div class="line">+--------+--------------+--------+--------+---------+------------+---------+--------+-------------+---------+</div><div class="line">| stuNo  | stuName      | stuSex | stuAge | stuSeat | stuAddress | examNo  | stuNo  | writtenExam | labExam |</div><div class="line">+--------+--------------+--------+--------+---------+------------+---------+--------+-------------+---------+</div><div class="line">| s25303 | 李斯文       | 女     |     22 |       2 | 北京       | s271811 | s25303 |          80 |      58 |</div><div class="line">| s25302 | 李文才       | 男     |     31 |       3 | 上海       | s271813 | s25302 |          50 |      90 |</div><div class="line">| s25304 | 欧阳俊雄     | 男     |     28 |       4 | 天津       | s271815 | s25304 |          65 |      50 |</div><div class="line">| s25301 | 张秋丽       | 男     |     18 |       1 | 北京       | s271816 | s25301 |          77 |      82 |</div><div class="line">| s25318 | 争青小子     | 男     |     26 |       6 | 天津       | s271819 | s25318 |          56 |      48 |</div><div class="line">| s25305 | 诸葛丽丽     | 女     |     23 |       7 | 河南       | NULL    | NULL   |        NULL |    NULL |</div><div class="line">| s25319 | 梅超风       | 女     |     23 |       5 | 河北       | NULL    | NULL   |        NULL |    NULL |</div><div class="line">+--------+--------------+--------+--------+---------+------------+---------+--------+-------------+---------+</div><div class="line">7 rows in set (0.00 sec)</div></pre></td></tr></table></figure>
<blockquote>
<p>select <em> from 表1 left join 表2 on 表1.公共字段=表2.公共字段和select </em> from 表2 left join 表1 on 表1.公共字段=表2.公共字段是否一样？<br>答：不一样，第一个SQL语句以表1为准，第二个SQL语句以表2为准</p>
</blockquote>
<h4 id="右外连接【right-join】"><a href="#右外连接【right-join】" class="headerlink" title="右外连接【right join】"></a>右外连接【right join】</h4><p>以右边的表为标准，如果右边的表没有对应的记录，用NULL填充。<br>语法：<code>select 列名 from 表1 right join 表2 on 表1.公共字段=表2.公共字段</code></p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div></pre></td><td class="code"><pre><div class="line">mysql&gt; select * from stuinfo right join stuMarks on stuinfo.stuNo=stuMarks.stuNo;</div><div class="line">+--------+--------------+--------+--------+---------+------------+---------+--------+-------------+---------+</div><div class="line">| stuNo  | stuName      | stuSex | stuAge | stuSeat | stuAddress | examNo  | stuNo  | writtenExam | labExam |</div><div class="line">+--------+--------------+--------+--------+---------+------------+---------+--------+-------------+---------+</div><div class="line">| s25303 | 李斯文       | 女     |     22 |       2 | 北京       | s271811 | s25303 |          80 |      58 |</div><div class="line">| s25302 | 李文才       | 男     |     31 |       3 | 上海       | s271813 | s25302 |          50 |      90 |</div><div class="line">| s25304 | 欧阳俊雄     | 男     |     28 |       4 | 天津       | s271815 | s25304 |          65 |      50 |</div><div class="line">| s25301 | 张秋丽       | 男     |     18 |       1 | 北京       | s271816 | s25301 |          77 |      82 |</div><div class="line">| s25318 | 争青小子     | 男     |     26 |       6 | 天津       | s271819 | s25318 |          56 |      48 |</div><div class="line">+--------+--------------+--------+--------+---------+------------+---------+--------+-------------+---------+</div><div class="line">5 rows in set (0.00 sec)</div></pre></td></tr></table></figure>
<blockquote>
<p>思考：select <em> from 表1 left join 表2 on 表1.公共字段=表2.公共字段和select </em> from 表2 right join 表1 on 表1.公共字段=表2.公共字段是否一样？<br>答：一样。</p>
</blockquote>
<h4 id="交叉连接【cross-join】"><a href="#交叉连接【cross-join】" class="headerlink" title="交叉连接【cross join】"></a>交叉连接【cross join】</h4><p>1、如果没有连接表达式返回的是笛卡尔积<br><code>mysql&gt; select * from stuinfo cross join stuMarks;</code><br>等价于<br><code>mysql&gt; select * from stuinfo,stuMarks;</code><br>2、如果有连接表达式等价于内连接</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div></pre></td><td class="code"><pre><div class="line">mysql&gt; select * from stuinfo cross join stuMarks on stuinfo.stuNo=stuMarks.stuNo;</div><div class="line">+--------+--------------+--------+--------+---------+------------+---------+--------+-------------+---------+</div><div class="line">| stuNo  | stuName      | stuSex | stuAge | stuSeat | stuAddress | examNo  | stuNo  | writtenExam | labExam |</div><div class="line">+--------+--------------+--------+--------+---------+------------+---------+--------+-------------+---------+</div><div class="line">| s25303 | 李斯文       | 女     |     22 |       2 | 北京       | s271811 | s25303 |          80 |      58 |</div><div class="line">| s25302 | 李文才       | 男     |     31 |       3 | 上海       | s271813 | s25302 |          50 |      90 |</div><div class="line">| s25304 | 欧阳俊雄     | 男     |     28 |       4 | 天津       | s271815 | s25304 |          65 |      50 |</div><div class="line">| s25301 | 张秋丽       | 男     |     18 |       1 | 北京       | s271816 | s25301 |          77 |      82 |</div><div class="line">| s25318 | 争青小子     | 男     |     26 |       6 | 天津       | s271819 | s25318 |          56 |      48 |</div><div class="line">+--------+--------------+--------+--------+---------+------------+---------+--------+-------------+---------+</div></pre></td></tr></table></figure>
<h4 id="自然连接【natural】"><a href="#自然连接【natural】" class="headerlink" title="自然连接【natural】"></a>自然连接【natural】</h4><p>自动的判断连接条件，它是通过同名字段来判断的<br>自然连接又分为：<br>1、    自然内连接        natural join<br><code>select * from stuinfo natural join stuMarks;</code><br>2、    自然左外连接        natural left join<br><code>select * from stuinfo natural left join stuMarks;</code><br>3、    自然右外连接        natural right join<br><code>select * from stuinfo natural rigth join stuMarks;</code></p>
<blockquote>
<p>自然连接结论：<br>1、    表连接通过同名的字段来连接的<br>2、    如果没有同名的字段返回笛卡尔积<br>3、    会对结果进行整理，整理的规则如下<br>a)    连接字段保留一个<br>b)    连接字段放在最前面</p>
</blockquote>
<h4 id="using"><a href="#using" class="headerlink" title="using()"></a>using()</h4><p>1、用来指定连接字段。<br>2、using()也会对连接字段进行整理，整理方式和自然连接是一样的。<br><code>select * from stuinfo inner join stuMarks using(id);</code></p>
<h3 id="子查询"><a href="#子查询" class="headerlink" title="子查询"></a>子查询</h3><p>1、    语法：select 语句 where 条件 (select … from 表)<br>2、    外面的查询称为父查询，括号中的查询称为子查询<br>3、    子查询为父查询提供查询条件</p>
<h4 id="子查询-1"><a href="#子查询-1" class="headerlink" title="=子查询"></a>=子查询</h4><p>使用表连接的方法</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div></pre></td><td class="code"><pre><div class="line">mysql&gt; select stuName,writtenExam from stuinfo natural join stuMarks having writtenExam&gt;60;</div><div class="line">+--------------+-------------+</div><div class="line">| stuName      | writtenExam |</div><div class="line">+--------------+-------------+</div><div class="line">| 李斯文       |          80 |</div><div class="line">| 欧阳俊雄     |          65 |</div><div class="line">| 张秋丽       |          77 |</div><div class="line">+--------------+-------------+</div><div class="line">3 rows in set (0.00 sec)</div></pre></td></tr></table></figure>
<p>使用子查询方法</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div></pre></td><td class="code"><pre><div class="line">mysql&gt; select * from stuinfo where stuNo in(select stuNo from stuMarks where writtenExam&gt;60);</div><div class="line">+--------+--------------+--------+--------+---------+------------+</div><div class="line">| stuNo  | stuName      | stuSex | stuAge | stuSeat | stuAddress |</div><div class="line">+--------+--------------+--------+--------+---------+------------+</div><div class="line">| s25303 | 李斯文       | 女     |     22 |       2 | 北京       |</div><div class="line">| s25304 | 欧阳俊雄     | 男     |     28 |       4 | 天津       |</div><div class="line">| s25301 | 张秋丽       | 男     |     18 |       1 | 北京       |</div><div class="line">+--------+--------------+--------+--------+---------+------------+</div><div class="line">3 rows in set (0.00 sec)</div></pre></td></tr></table></figure>
<p>找出最高分</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div><div class="line">13</div><div class="line">14</div><div class="line">15</div></pre></td><td class="code"><pre><div class="line">mysql&gt; select * from stuinfo where stuNo=(select stuNo from stuMarks order by writtenExam desc limit 1);</div><div class="line">+--------+-----------+--------+--------+---------+------------+</div><div class="line">| stuNo  | stuName   | stuSex | stuAge | stuSeat | stuAddress |</div><div class="line">+--------+-----------+--------+--------+---------+------------+</div><div class="line">| s25303 | 李斯文    | 女     |     22 |       2 | 北京       |</div><div class="line">+--------+-----------+--------+--------+---------+------------+</div><div class="line">1 row in set (0.00 sec)</div><div class="line"></div><div class="line">mysql&gt; select * from stuinfo where stuNo=(select stuNo from stuMarks where writtenExam=(select max(writtenExam) from stuMarks));</div><div class="line">+--------+-----------+--------+--------+---------+------------+</div><div class="line">| stuNo  | stuName   | stuSex | stuAge | stuSeat | stuAddress |</div><div class="line">+--------+-----------+--------+--------+---------+------------+</div><div class="line">| s25303 | 李斯文    | 女     |     22 |       2 | 北京       |</div><div class="line">+--------+-----------+--------+--------+---------+------------+</div><div class="line">1 row in set (0.00 sec)</div></pre></td></tr></table></figure>
<h4 id="in-not-in子查询"><a href="#in-not-in子查询" class="headerlink" title="in|not in子查询"></a>in|not in子查询</h4><p>用于子查询的返回结果多个值</p>
<h4 id="some-、any-、all"><a href="#some-、any-、all" class="headerlink" title="some()、any()、all()"></a>some()、any()、all()</h4><p>some:一些<br>any:一些        和some()是一样的  类似于in<br>all:全部</p>
<p>=some（=any） 等于其中任何一个<br>!=some（!=any）不等于其中任何一个就可以了<br>=all 等于其中的所有<br>!=all 不等于其中的所有</p>
<blockquote>
<p>思考：!=some()和not in一样吗？<br>答：不一样；!=some()表示父查询的记录只要不等于some中的任意一条即可。<br>!=all()才和not in一样</p>
</blockquote>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div><div class="line">13</div><div class="line">14</div><div class="line">15</div><div class="line">16</div><div class="line">17</div><div class="line">18</div><div class="line">19</div><div class="line">20</div><div class="line">21</div><div class="line">22</div></pre></td><td class="code"><pre><div class="line">mysql&gt; select * from stuinfo where stuNo=some(select stuNo from stuMarks where writtenExam&gt;60);</div><div class="line">+--------+--------------+--------+--------+---------+------------+</div><div class="line">| stuNo  | stuName      | stuSex | stuAge | stuSeat | stuAddress |</div><div class="line">+--------+--------------+--------+--------+---------+------------+</div><div class="line">| s25303 | 李斯文       | 女     |     22 |       2 | 北京       |</div><div class="line">| s25304 | 欧阳俊雄     | 男     |     28 |       4 | 天津       |</div><div class="line">| s25301 | 张秋丽       | 男     |     18 |       1 | 北京       |</div><div class="line">+--------+--------------+--------+--------+---------+------------+</div><div class="line">3 rows in set (0.00 sec)</div><div class="line"></div><div class="line">mysql&gt; select * from stuinfo where stuNo=any(select stuNo from stuMarks where writtenExam&gt;60);</div><div class="line">+--------+--------------+--------+--------+---------+------------+</div><div class="line">| stuNo  | stuName      | stuSex | stuAge | stuSeat | stuAddress |</div><div class="line">+--------+--------------+--------+--------+---------+------------+</div><div class="line">| s25303 | 李斯文       | 女     |     22 |       2 | 北京       |</div><div class="line">| s25304 | 欧阳俊雄     | 男     |     28 |       4 | 天津       |</div><div class="line">| s25301 | 张秋丽       | 男     |     18 |       1 | 北京       |</div><div class="line">+--------+--------------+--------+--------+---------+------------+</div><div class="line">3 rows in set (0.00 sec)</div><div class="line"></div><div class="line">mysql&gt; select * from stuinfo where stuNo=all(select stuNo from stuMarks where writtenExam&gt;60);</div><div class="line">Empty set (0.00 sec)</div></pre></td></tr></table></figure>
<h4 id="exists和not-exists"><a href="#exists和not-exists" class="headerlink" title="exists和not exists"></a>exists和not exists</h4><p>如果有人大于等于80分就显示所有的学生</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div><div class="line">13</div></pre></td><td class="code"><pre><div class="line">mysql&gt; select * from stuinfo where exists (select * from stuMarks where writtenExam&gt;=80);</div><div class="line">+--------+--------------+--------+--------+---------+------------+</div><div class="line">| stuNo  | stuName      | stuSex | stuAge | stuSeat | stuAddress |</div><div class="line">+--------+--------------+--------+--------+---------+------------+</div><div class="line">| s25301 | 张秋丽       | 男     |     18 |       1 | 北京       |</div><div class="line">| s25302 | 李文才       | 男     |     31 |       3 | 上海       |</div><div class="line">| s25303 | 李斯文       | 女     |     22 |       2 | 北京       |</div><div class="line">| s25304 | 欧阳俊雄     | 男     |     28 |       4 | 天津       |</div><div class="line">| s25305 | 诸葛丽丽     | 女     |     23 |       7 | 河南       |</div><div class="line">| s25318 | 争青小子     | 男     |     26 |       6 | 天津       |</div><div class="line">| s25319 | 梅超风       | 女     |     23 |       5 | 河北       |</div><div class="line">+--------+--------------+--------+--------+---------+------------+</div><div class="line">7 rows in set (0.00 sec)</div></pre></td></tr></table></figure>
<p>如果没有人超过80分就显示所有的学生</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line">mysql&gt; select * from stuinfo where not exists (select * from stuMarks where writtenExam&gt;=80);</div><div class="line">Empty set (0.00 sec)</div></pre></td></tr></table></figure>
<h4 id="子查询分类"><a href="#子查询分类" class="headerlink" title="子查询分类"></a>子查询分类</h4><p>1、    标量子查询：子查询返回的结果就一个<br>2、    列子查询：子查询返回的结果是一个列表<br>3、    行子查询：子查询返回的结果返回一行<br>4、    表子查询：子查询返回的结果当成一个表(一定要取别名)</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div></pre></td><td class="code"><pre><div class="line">mysql&gt; select *,count(*) from (select * from stuinfo order by stuAge desc) as new1 group by stuSex;</div><div class="line">+--------+--------------+--------+--------+---------+------------+----------+</div><div class="line">| stuNo  | stuName      | stuSex | stuAge | stuSeat | stuAddress | count(*) |</div><div class="line">+--------+--------------+--------+--------+---------+------------+----------+</div><div class="line">| s25305 | 诸葛丽丽     | 女     |     23 |       7 | 河南       |        3 |</div><div class="line">| s25302 | 李文才       | 男     |     31 |       3 | 上海       |        4 |</div><div class="line">+--------+--------------+--------+--------+---------+------------+----------+</div><div class="line">2 rows in set (0.00 sec)</div></pre></td></tr></table></figure>
<blockquote>
<p>子查询一定要取别名</p>
</blockquote>
<h3 id="视图【view】"><a href="#视图【view】" class="headerlink" title="视图【view】"></a>视图【view】</h3><p>1、    视图是一张虚拟表，它表示一张表的部分或多张表的综合的结构。<br>2、    视图仅仅是表结构，没有表数据。视图的结构和数据建立在表的基础上。</p>
<h4 id="创建视图"><a href="#创建视图" class="headerlink" title="创建视图"></a>创建视图</h4><p>语法</p>
<p>create [or replace] view 视图的名称<br>as<br>    select语句</p>
<p>因为视图是一个表结构，所以创建视图后，会在数据库文件夹中多一个与视图名同名的.frm文件</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div></pre></td><td class="code"><pre><div class="line">mysql&gt; create view vw_stu</div><div class="line">    -&gt; as</div><div class="line">    -&gt;   select * from stuinfo nutural join stumarks using(stuno);</div><div class="line">Query OK, 0 rows affected (0.02 sec)</div></pre></td></tr></table></figure>
<h4 id="查询视图"><a href="#查询视图" class="headerlink" title="查询视图"></a>查询视图</h4><p>视图是一张虚拟表，视图的用法和表的用法一样</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div></pre></td><td class="code"><pre><div class="line">mysql&gt; select * from vw_stu;</div><div class="line">+--------+--------------+--------+--------+---------+------------+---------+-------------+---------+</div><div class="line">| stuNo  | stuName      | stuSex | stuAge | stuSeat | stuAddress | examNo  | writtenExam | labExam |</div><div class="line">+--------+--------------+--------+--------+---------+------------+---------+-------------+---------+</div><div class="line">| s25303 | 李斯文       | 女     |     22 |       2 | 北京       | s271811 |          80 |      58 |</div><div class="line">| s25302 | 李文才       | 男     |     31 |       3 | 上海       | s271813 |          50 |      90 |</div><div class="line">| s25304 | 欧阳俊雄     | 男     |     28 |       4 | 天津       | s271815 |          65 |      50 |</div><div class="line">| s25301 | 张秋丽       | 男     |     18 |       1 | 北京       | s271816 |          77 |      82 |</div><div class="line">| s25318 | 争青小子     | 男     |     26 |       6 | 天津       | s271819 |          56 |      48 |</div><div class="line">+--------+--------------+--------+--------+---------+------------+---------+-------------+---------+</div><div class="line">5 rows in set (0.00 sec)</div></pre></td></tr></table></figure>
<h4 id="查看视图的结构"><a href="#查看视图的结构" class="headerlink" title="查看视图的结构"></a>查看视图的结构</h4><figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div><div class="line">13</div><div class="line">14</div><div class="line">15</div></pre></td><td class="code"><pre><div class="line">mysql&gt; desc vw_stu;</div><div class="line">+-------------+-------------+------+-----+---------+-------+</div><div class="line">| Field       | Type        | Null | Key | Default | Extra |</div><div class="line">+-------------+-------------+------+-----+---------+-------+</div><div class="line">| stuNo       | char(6)     | NO   |     | NULL    |       |</div><div class="line">| stuName     | varchar(10) | NO   |     | NULL    |       |</div><div class="line">| stuSex      | char(2)     | NO   |     | NULL    |       |</div><div class="line">| stuAge      | tinyint(4)  | NO   |     | NULL    |       |</div><div class="line">| stuSeat     | tinyint(4)  | NO   |     | NULL    |       |</div><div class="line">| stuAddress  | varchar(10) | NO   |     | NULL    |       |</div><div class="line">| examNo      | char(7)     | NO   |     | NULL    |       |</div><div class="line">| writtenExam | int(11)     | YES  |     | NULL    |       |</div><div class="line">| labExam     | int(11)     | YES  |     | NULL    |       |</div><div class="line">+-------------+-------------+------+-----+---------+-------+</div><div class="line">9 rows in set (0.02 sec)</div></pre></td></tr></table></figure>
<h4 id="查看创建视图的语法"><a href="#查看创建视图的语法" class="headerlink" title="查看创建视图的语法"></a>查看创建视图的语法</h4><figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div></pre></td><td class="code"><pre><div class="line">mysql&gt; show create view vw_stu \g</div><div class="line">+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+</div><div class="line">| View   | Create View                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                | character_set_client | collation_connection |</div><div class="line">+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+</div><div class="line">| vw_stu | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vw_stu` AS select `nutural`.`stuNo` AS `stuNo`,`nutural`.`stuName` AS `stuName`,`nutural`.`stuSex` AS `stuSex`,`nutural`.`stuAge` AS `stuAge`,`nutural`.`stuSeat` AS `stuSeat`,`nutural`.`stuAddress` AS `stuAddress`,`stumarks`.`examNo` AS `examNo`,`stumarks`.`writtenExam` AS `writtenExam`,`stumarks`.`labExam` AS `labExam` from (`stuinfo` `nutural` join `stumarks` on((`nutural`.`stuNo` = `stumarks`.`stuNo`))) | utf8                 | utf8_general_ci      |</div><div class="line">+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+</div><div class="line">1 row in set (0.00 sec)</div></pre></td></tr></table></figure>
<h4 id="显示所有视图"><a href="#显示所有视图" class="headerlink" title="显示所有视图"></a>显示所有视图</h4><figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div></pre></td><td class="code"><pre><div class="line">mysql&gt; show tables;</div><div class="line">+-----------------+</div><div class="line">| Tables_in_uiste |</div><div class="line">+-----------------+</div><div class="line">| stuMarks        |</div><div class="line">| stuinfo         |</div><div class="line">| vw_stu          |</div><div class="line">+-----------------+</div><div class="line">3 rows in set (0.01 sec)</div></pre></td></tr></table></figure>
<h4 id="精确查找视图"><a href="#精确查找视图" class="headerlink" title="精确查找视图"></a>精确查找视图</h4><p>方法一：information_schema中的views表保存的是视图信息</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div></pre></td><td class="code"><pre><div class="line">mysql&gt; select table_name from information_schema.views;</div><div class="line">+------------+</div><div class="line">| table_name |</div><div class="line">+------------+</div><div class="line">| vw_stu     |</div><div class="line">+------------+</div><div class="line">1 row in set (0.12 sec)</div></pre></td></tr></table></figure>
<p>方法二：show table status查看表的属性</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div><div class="line">13</div><div class="line">14</div><div class="line">15</div><div class="line">16</div><div class="line">17</div><div class="line">18</div><div class="line">19</div><div class="line">20</div><div class="line">21</div><div class="line">22</div><div class="line">23</div><div class="line">24</div><div class="line">25</div><div class="line">26</div><div class="line">27</div><div class="line">28</div><div class="line">29</div><div class="line">30</div><div class="line">31</div><div class="line">32</div><div class="line">33</div><div class="line">34</div><div class="line">35</div><div class="line">36</div><div class="line">37</div><div class="line">38</div><div class="line">39</div><div class="line">40</div><div class="line">41</div><div class="line">42</div><div class="line">43</div><div class="line">44</div><div class="line">45</div><div class="line">46</div><div class="line">47</div><div class="line">48</div><div class="line">49</div><div class="line">50</div><div class="line">51</div><div class="line">52</div><div class="line">53</div><div class="line">54</div><div class="line">55</div><div class="line">56</div><div class="line">57</div><div class="line">58</div><div class="line">59</div></pre></td><td class="code"><pre><div class="line">mysql&gt; show table status\G</div><div class="line">*************************** 1. row ***************************</div><div class="line">           Name: stuMarks</div><div class="line">         Engine: InnoDB</div><div class="line">        Version: 10</div><div class="line">     Row_format: Compact</div><div class="line">           Rows: 5</div><div class="line"> Avg_row_length: 3276</div><div class="line">    Data_length: 16384</div><div class="line">Max_data_length: 0</div><div class="line">   Index_length: 0</div><div class="line">      Data_free: 0</div><div class="line"> Auto_increment: NULL</div><div class="line">    Create_time: 2016-08-31 17:18:54</div><div class="line">    Update_time: NULL</div><div class="line">     Check_time: NULL</div><div class="line">      Collation: utf8_general_ci</div><div class="line">       Checksum: NULL</div><div class="line"> Create_options: </div><div class="line">        Comment: </div><div class="line">*************************** 2. row ***************************</div><div class="line">           Name: stuinfo</div><div class="line">         Engine: InnoDB</div><div class="line">        Version: 10</div><div class="line">     Row_format: Compact</div><div class="line">           Rows: 7</div><div class="line"> Avg_row_length: 2340</div><div class="line">    Data_length: 16384</div><div class="line">Max_data_length: 0</div><div class="line">   Index_length: 0</div><div class="line">      Data_free: 0</div><div class="line"> Auto_increment: NULL</div><div class="line">    Create_time: 2016-08-31 17:18:54</div><div class="line">    Update_time: NULL</div><div class="line">     Check_time: NULL</div><div class="line">      Collation: utf8_general_ci</div><div class="line">       Checksum: NULL</div><div class="line"> Create_options: </div><div class="line">        Comment: </div><div class="line">*************************** 3. row ***************************</div><div class="line">           Name: vw_stu</div><div class="line">         Engine: NULL</div><div class="line">        Version: NULL</div><div class="line">     Row_format: NULL</div><div class="line">           Rows: NULL</div><div class="line"> Avg_row_length: NULL</div><div class="line">    Data_length: NULL</div><div class="line">Max_data_length: NULL</div><div class="line">   Index_length: NULL</div><div class="line">      Data_free: NULL</div><div class="line"> Auto_increment: NULL</div><div class="line">    Create_time: NULL</div><div class="line">    Update_time: NULL</div><div class="line">     Check_time: NULL</div><div class="line">      Collation: NULL</div><div class="line">       Checksum: NULL</div><div class="line"> Create_options: NULL</div><div class="line">        Comment: VIEW</div><div class="line">3 rows in set (0.00 sec)</div></pre></td></tr></table></figure>
<p>显示所有视图的语法是</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div><div class="line">13</div><div class="line">14</div><div class="line">15</div><div class="line">16</div><div class="line">17</div><div class="line">18</div><div class="line">19</div><div class="line">20</div><div class="line">21</div></pre></td><td class="code"><pre><div class="line">mysql&gt; show table status where comment=&apos;view&apos;\G</div><div class="line">*************************** 1. row ***************************</div><div class="line">           Name: vw_stu</div><div class="line">         Engine: NULL</div><div class="line">        Version: NULL</div><div class="line">     Row_format: NULL</div><div class="line">           Rows: NULL</div><div class="line"> Avg_row_length: NULL</div><div class="line">    Data_length: NULL</div><div class="line">Max_data_length: NULL</div><div class="line">   Index_length: NULL</div><div class="line">      Data_free: NULL</div><div class="line"> Auto_increment: NULL</div><div class="line">    Create_time: NULL</div><div class="line">    Update_time: NULL</div><div class="line">     Check_time: NULL</div><div class="line">      Collation: NULL</div><div class="line">       Checksum: NULL</div><div class="line"> Create_options: NULL</div><div class="line">        Comment: VIEW</div><div class="line">1 row in set (0.00 sec)</div></pre></td></tr></table></figure>
<h4 id="更改视图"><a href="#更改视图" class="headerlink" title="更改视图"></a>更改视图</h4><p>语法：<br>alter view 视图名<br>as<br>    select 语句</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div></pre></td><td class="code"><pre><div class="line">mysql&gt; alter view vw_stu</div><div class="line">    -&gt; as</div><div class="line">    -&gt;  select stuaddress,count(stuaddress) from stuinfo left join stumarks using(stuno) group by stusex;         Query OK, 0 rows affected (0.02 sec)</div><div class="line">mysql&gt; select * from vw_stu;</div><div class="line">+------------+-------------------+</div><div class="line">| stuaddress | count(stuaddress) |</div><div class="line">+------------+-------------------+</div><div class="line">| 北京       |                 3 |</div><div class="line">| 上海       |                 4 |</div><div class="line">+------------+-------------------+</div><div class="line">2 rows in set (0.02 sec)</div></pre></td></tr></table></figure>
<h4 id="删除视图"><a href="#删除视图" class="headerlink" title="删除视图"></a>删除视图</h4><p>语法：<code>drop view [if exists] 视图1,视图2,…</code></p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div></pre></td><td class="code"><pre><div class="line">mysql&gt; drop view if exists vw_stu;</div><div class="line">Query OK, 0 rows affected (0.01 sec)</div><div class="line"></div><div class="line">mysql&gt; select table_name from information_schema.views;</div><div class="line">Empty set (0.03 sec)</div></pre></td></tr></table></figure>
<h4 id="视图的作用"><a href="#视图的作用" class="headerlink" title="视图的作用"></a>视图的作用</h4><p>1、    筛选数据，防止未经许可访问敏感数据<br>2、    增加表的兼容性<br>3、    隐藏表结构<br>4、    降低SQL语句的复杂度</p>
<h4 id="视图的算法"><a href="#视图的算法" class="headerlink" title="视图的算法"></a>视图的算法</h4><p>找出语文成绩最高的男生和女生<br>既然子查询当成一个表，我们可以用视图来代替<br>将子查询写法改为如下</p>
<blockquote>
<p>结果不正确！原因：这是因为视图的算法造成的</p>
</blockquote>
<h4 id="视图的算法-1"><a href="#视图的算法-1" class="headerlink" title="视图的算法"></a>视图的算法</h4><p>1、    merge：合并算法，将视图的语句和外层的语句合并后在执行。<br>2、    temptable：临时表算法，将视图生成一个临时表，再执行外层语句<br>3、    undefined：未定义，MySQL到底用merge还是用temptable由MySQL决<br>定，这是一个默认的算法，一般视图都会选择merge算法，因为merge效率高。</p>
<h4 id="解决"><a href="#解决" class="headerlink" title="解决"></a>解决</h4><p>在创建视图的时候指定视图的算法<br>create view algorithm=temptable 视图名<br>as<br>    select 语句    </p>
<p>重写视图</p>
<p>重新执行查询</p>
<h3 id="事务安全【transaction】"><a href="#事务安全【transaction】" class="headerlink" title="事务安全【transaction】"></a>事务安全【transaction】</h3><p>1、    事务是一个不可分割的执行单元<br>2、    事务作为一个整体要么一起执行，要么一起回滚</p>
<h4 id="开启事务1"><a href="#开启事务1" class="headerlink" title="开启事务1"></a>开启事务1</h4><p>语法：<code>start transaction或begin [work]</code></p>
<h4 id="执行sql更新语句2"><a href="#执行sql更新语句2" class="headerlink" title="执行sql更新语句2"></a>执行sql更新语句2</h4><p><code>update 表名 set 字段名=字段内容 条件</code></p>
<h4 id="设置还原点3"><a href="#设置还原点3" class="headerlink" title="设置还原点3"></a>设置还原点3</h4><p><code>savepoint sp1</code></p>
<h4 id="执行sql更新语句4"><a href="#执行sql更新语句4" class="headerlink" title="执行sql更新语句4"></a>执行sql更新语句4</h4><h4 id="成功提交事务5"><a href="#成功提交事务5" class="headerlink" title="成功提交事务5"></a>成功提交事务5</h4><p>语法：commit</p>
<h4 id="失败回滚事务5"><a href="#失败回滚事务5" class="headerlink" title="失败回滚事务5"></a>失败回滚事务5</h4><p>语法：rollback</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div><div class="line">13</div><div class="line">14</div><div class="line">15</div><div class="line">16</div><div class="line">17</div><div class="line">18</div><div class="line">19</div><div class="line">20</div><div class="line">21</div><div class="line">22</div><div class="line">23</div><div class="line">24</div><div class="line">25</div><div class="line">26</div><div class="line">27</div><div class="line">28</div><div class="line">29</div><div class="line">30</div><div class="line">31</div><div class="line">32</div><div class="line">33</div><div class="line">34</div><div class="line">35</div><div class="line">36</div><div class="line">37</div><div class="line">38</div><div class="line">39</div><div class="line">40</div><div class="line">41</div><div class="line">42</div><div class="line">43</div><div class="line">44</div><div class="line">45</div><div class="line">46</div><div class="line">47</div><div class="line">48</div><div class="line">49</div></pre></td><td class="code"><pre><div class="line">mysql&gt; start transaction;</div><div class="line">Query OK, 0 rows affected (0.00 sec)</div><div class="line"></div><div class="line">mysql&gt; insert into t1 values (4,4);</div><div class="line">Query OK, 1 row affected (0.00 sec)</div><div class="line"></div><div class="line">mysql&gt; select * from t1;</div><div class="line">+------+------+</div><div class="line">| id   | num1 |</div><div class="line">+------+------+</div><div class="line">|    1 |    1 |</div><div class="line">|    2 |    2 |</div><div class="line">|    3 |    3 |</div><div class="line">|    4 |    4 |</div><div class="line">+------+------+</div><div class="line">4 rows in set (0.00 sec)</div><div class="line"></div><div class="line">mysql&gt; rollback;</div><div class="line">Query OK, 0 rows affected (0.00 sec)</div><div class="line"></div><div class="line">mysql&gt; select * from t1;</div><div class="line">+------+------+</div><div class="line">| id   | num1 |</div><div class="line">+------+------+</div><div class="line">|    1 |    1 |</div><div class="line">|    2 |    2 |</div><div class="line">|    3 |    3 |</div><div class="line">+------+------+</div><div class="line">3 rows in set (0.00 sec)</div><div class="line"></div><div class="line">mysql&gt; begin;</div><div class="line">Query OK, 0 rows affected (0.00 sec)</div><div class="line"></div><div class="line">mysql&gt; insert into t1 values (4,4);</div><div class="line">Query OK, 1 row affected (0.00 sec)</div><div class="line"></div><div class="line">mysql&gt; commit;</div><div class="line">Query OK, 0 rows affected (0.00 sec)</div><div class="line"></div><div class="line">mysql&gt; select * from t1;</div><div class="line">+------+------+</div><div class="line">| id   | num1 |</div><div class="line">+------+------+</div><div class="line">|    1 |    1 |</div><div class="line">|    2 |    2 |</div><div class="line">|    3 |    3 |</div><div class="line">|    4 |    4 |</div><div class="line">+------+------+</div><div class="line">4 rows in set (0.00 sec)</div></pre></td></tr></table></figure>
<blockquote>
<p>思考：事务什么时候产生？什么时候结束？<br>答：开启的时候产生，提交事务或回滚事务都结束<br>脚下留心：只有innodb和BDB才支持事务，myisam不支持事务。</p>
</blockquote>
<h4 id="事务的特性（ACID）"><a href="#事务的特性（ACID）" class="headerlink" title="事务的特性（ACID）"></a>事务的特性（ACID）</h4><p>1、    原子性（Atomicity）：事务是一个整体，不可以再分，要么一起执行，要么一起不执行。<br>2、    一致性（Consistency）：事务完成时，数据必须处于一致的状态。<br>3、    隔离性（Isolation）：每个事务都是相互隔离的<br>4、    永久性（Durability）：事务完成后，对数据的修改是永久性的。</p>
<h4 id="自动提交事务"><a href="#自动提交事务" class="headerlink" title="自动提交事务"></a>自动提交事务</h4><p>默认情况下，每个独立的SQL语句都在自动提交事务的。<br>通过如下语句查看是否自动提交事务</p>
<p>可以更改自动提交事务<br>set autocommit=0 | 1；</p>
<h4 id="MySQL的锁的机制"><a href="#MySQL的锁的机制" class="headerlink" title="MySQL的锁的机制"></a>MySQL的锁的机制</h4><p>当在对数据表进行并发(同一时刻会有多个客户端操作同一条记录)操作时，就会触发MySQL的锁的机制<br>①、表锁当某个客户端对一个表进行更改操作时会锁住整个表(其他客户端只能等待)非键值<br>②、行锁–键值</p>
<h3 id="索引【index】"><a href="#索引【index】" class="headerlink" title="索引【index】"></a>索引【index】</h3><p>索引的优点：查询速度快<br>索引的缺点：<br>1、    增、删、改（数据操作语句）效率低了<br>2、    索引占用空间</p>
<h4 id="索引的类型"><a href="#索引的类型" class="headerlink" title="索引的类型"></a>索引的类型</h4><p>1、    普通索引<br>2、    唯一索引（唯一键）<br>3、    主键索引：只要主键就自动创建主键索引，不需要手动创建。<br>4、    全文索引，搜索引擎使用，MySQL不支持中文的全文索引，我们通过sphinx去解决中文的全文索引。</p>
<h4 id="创建普通索引【create-index】"><a href="#创建普通索引【create-index】" class="headerlink" title="创建普通索引【create index】"></a>创建普通索引【create index】</h4><p>语法：<code>create index 索引名 on 表名 （字段名）</code></p>
<p>创建索引<br>查看索引</p>
<h4 id="通过修改表的方式创建索引"><a href="#通过修改表的方式创建索引" class="headerlink" title="通过修改表的方式创建索引"></a>通过修改表的方式创建索引</h4><p>语法：<code>alter table 表名 add index [索引的名称] （列名）</code><br>创建索引</p>
<h4 id="创建表的时候指定索引"><a href="#创建表的时候指定索引" class="headerlink" title="创建表的时候指定索引"></a>创建表的时候指定索引</h4><figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div><div class="line">13</div><div class="line">14</div><div class="line">15</div><div class="line">16</div><div class="line">17</div><div class="line">18</div><div class="line">19</div><div class="line">20</div></pre></td><td class="code"><pre><div class="line">mysql&gt; create table temp(</div><div class="line">    -&gt; id int,</div><div class="line">    -&gt; name varchar(20),</div><div class="line">    -&gt; age tinyint,</div><div class="line">    -&gt; index ix_temp_name(name),		--创建索引</div><div class="line">    -&gt; key(age)				--创建索引</div><div class="line">    -&gt; );</div><div class="line">Query OK, 0 rows affected (0.00 sec)</div><div class="line"></div><div class="line">mysql&gt; show create table temp\G</div><div class="line">*************************** 1. row ***************************</div><div class="line">       Table: temp</div><div class="line">Create Table: CREATE TABLE `temp` (</div><div class="line">  `id` int(11) DEFAULT NULL,</div><div class="line">  `name` varchar(20) DEFAULT NULL,</div><div class="line">  `age` tinyint(4) DEFAULT NULL,</div><div class="line">  KEY `ix_temp_name` (`name`),</div><div class="line">  KEY `age` (`age`)</div><div class="line">) ENGINE=InnoDB DEFAULT CHARSET=utf8</div><div class="line">1 row in set (0.00 sec)</div></pre></td></tr></table></figure>
<h4 id="创建唯一索引"><a href="#创建唯一索引" class="headerlink" title="创建唯一索引"></a>创建唯一索引</h4><p>语法一：create unique index 索引名 on 表名 （字段名）<br>语法二：alter table 表名 add unqiue index [索引的名称] （列名）<br>语法三：创建表的时候添加唯一索引，和创建唯一键是一样的。</p>
<h4 id="创建多列组合索引"><a href="#创建多列组合索引" class="headerlink" title="创建多列组合索引"></a>创建多列组合索引</h4><h4 id="删除索引"><a href="#删除索引" class="headerlink" title="删除索引"></a>删除索引</h4><p>语法：<code>drop index 索引名 on 表名</code></p>
<h4 id="创建索引的指导原则"><a href="#创建索引的指导原则" class="headerlink" title="创建索引的指导原则"></a>创建索引的指导原则</h4><p>1、    该列用于频繁搜索<br>2、    改列用于排序<br>3、    公共字段要创建索引<br>4、    如果表中的数据很少，不需要创建索引。MySQL搜索索引的时间比逐条搜索数据的时间要长。<br>5、    如果一个字段上的数据只有几个不同的值，改字段不适合做索引，比如性别。</p>
<h3 id="存储过程【procedure】"><a href="#存储过程【procedure】" class="headerlink" title="存储过程【procedure】"></a>存储过程【procedure】</h3><h4 id="存储过程的优点"><a href="#存储过程的优点" class="headerlink" title="存储过程的优点"></a>存储过程的优点</h4><p>1、    存储过程可以减少网络流量<br>2、    允许模块化设计<br>3、    支持事务</p>
<h4 id="创建存储过程"><a href="#创建存储过程" class="headerlink" title="创建存储过程"></a>创建存储过程</h4><p>语法：<br>create procedure 存储过程名(参数)<br>begin<br>    //sql语句<br>end;</p>
<blockquote>
<p>脚下留心：由于过程中有很多SQL语句，每个语句的结束都要用（；）表示。默认情况下，分号既表示语句结束，又表示向服务器发送SQL语句。我们希望分号仅表示语句的结束，不要将SQL语句发送到服务器执行，通过delimiter来更改结束符。</p>
</blockquote>
<p><code>mysql&gt;delimiter //</code></p>
<h4 id="创建简单的存储过程"><a href="#创建简单的存储过程" class="headerlink" title="创建简单的存储过程"></a>创建简单的存储过程</h4><h4 id="调用存储过程"><a href="#调用存储过程" class="headerlink" title="调用存储过程"></a>调用存储过程</h4><p>语法：<code>call 存储过程名()</code></p>
<h4 id="删除存储过程"><a href="#删除存储过程" class="headerlink" title="删除存储过程"></a>删除存储过程</h4><p>语法：<code>drop procedure [if exists] 存储过程名</code></p>
<h4 id="创建复杂的存储过程"><a href="#创建复杂的存储过程" class="headerlink" title="创建复杂的存储过程"></a>创建复杂的存储过程</h4><figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div><div class="line">13</div><div class="line">14</div><div class="line">15</div><div class="line">16</div><div class="line">17</div><div class="line">18</div><div class="line">19</div><div class="line">20</div><div class="line">21</div></pre></td><td class="code"><pre><div class="line">mysql&gt; create procedure proc(param char(6))</div><div class="line">    -&gt; begin</div><div class="line">    -&gt;      select * from stuinfo where stuno=param;</div><div class="line">    -&gt;      select * from stumarks where stuno=param;</div><div class="line">    -&gt; end //</div><div class="line">Query OK, 0 rows affected (0.00 sec)</div><div class="line"></div><div class="line">mysql&gt; call proc(&apos;s25301&apos;) //</div><div class="line">+--------+---------+--------+--------+---------+------------+</div><div class="line">| stuNo  | stuName | stuSex | stuAge | stuSeat | stuAddress |</div><div class="line">+--------+---------+--------+--------+---------+------------+</div><div class="line">| s25301 | 张秋丽        | 男       |     18 |       1 | 北京</div><div class="line">+--------+---------+--------+--------+---------+------------+</div><div class="line">1 row in set (0.00 sec)</div><div class="line"></div><div class="line">+---------+--------+-------------+---------+</div><div class="line">| examNo  | stuNo  | writtenExam | labExam |</div><div class="line">+---------+--------+-------------+---------+</div><div class="line">| s271816 | s25301 |          77 |      82 |</div><div class="line">+---------+--------+-------------+---------+</div><div class="line">1 row in set (0.01 sec)</div></pre></td></tr></table></figure>
<h4 id="存储过程的参数"><a href="#存储过程的参数" class="headerlink" title="存储过程的参数"></a>存储过程的参数</h4><p>存储过程不能使用return返回值，要返回值只能通过“输出参数”来向外传递值。<br>存储过程的参数分为：输入参数（in）【默认】，输出参数（out），输入输出参数（inout）</p>
<h3 id="变量"><a href="#变量" class="headerlink" title="变量"></a>变量</h3><h4 id="局部变量"><a href="#局部变量" class="headerlink" title="局部变量"></a>局部变量</h4><p>1、    通过declare关键字来声明变量<br>2、    语法：declare 变量名 数据类型 [default 默认值]<br>3、    使用set或select …into…给变量赋值</p>
<p>使用select…into…给变量赋值<br><figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div><div class="line">13</div><div class="line">14</div><div class="line">15</div><div class="line">16</div></pre></td><td class="code"><pre><div class="line">mysql&gt; create procedure proc(in id char(6))</div><div class="line">    -&gt; begin</div><div class="line">    -&gt;      declare name varchar(20);</div><div class="line">    -&gt;      declare sex char(1);</div><div class="line">    -&gt;      select stuname,stusex into name,sex from stuinfo where stuno=id;</div><div class="line">    -&gt;      select name,sex from dual;</div><div class="line">    -&gt; end //</div><div class="line">Query OK, 0 rows affected (0.00 sec)</div><div class="line"></div><div class="line">mysql&gt; call proc(&apos;s25301&apos;)//</div><div class="line">+--------+------+</div><div class="line">| name   | sex  |</div><div class="line">+--------+------+</div><div class="line">| 张秋丽       | 男     |</div><div class="line">+--------+------+</div><div class="line">1 row in set (0.00 sec)</div></pre></td></tr></table></figure></p>
<p>例题：查找同桌<br>例题：使用set赋值</p>
<h4 id="全局变量"><a href="#全局变量" class="headerlink" title="全局变量"></a>全局变量</h4><p>在变量前面加一个@，就是全局变量<br>全局变量是一个弱类型的变量，它的类型取决于付给变量的值</p>
<h4 id="系统变量"><a href="#系统变量" class="headerlink" title="系统变量"></a>系统变量</h4><p>只要变量名前面有两个@@的肯定是系统变量</p>
<h3 id="带输出参数的存储过程【out】"><a href="#带输出参数的存储过程【out】" class="headerlink" title="带输出参数的存储过程【out】"></a>带输出参数的存储过程【out】</h3><figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div><div class="line">13</div><div class="line">14</div><div class="line">15</div></pre></td><td class="code"><pre><div class="line">mysql&gt; create procedure proc(num int,out result int)</div><div class="line">    -&gt; begin</div><div class="line">    -&gt;      set result=num*num;</div><div class="line">    -&gt; end //</div><div class="line">Query OK, 0 rows affected (0.00 sec)</div><div class="line"></div><div class="line">mysql&gt; call proc(5,@result) //</div><div class="line">Query OK, 0 rows affected (0.00 sec)</div><div class="line"></div><div class="line">mysql&gt; select @result from dual //</div><div class="line">+---------+</div><div class="line">| @result |</div><div class="line">+---------+</div><div class="line">|      25 |</div><div class="line">+---------+</div></pre></td></tr></table></figure>
<h3 id="带输入输出参数的存储过程【inout】"><a href="#带输入输出参数的存储过程【inout】" class="headerlink" title="带输入输出参数的存储过程【inout】"></a>带输入输出参数的存储过程【inout】</h3><h3 id="查看存储过程的信息"><a href="#查看存储过程的信息" class="headerlink" title="查看存储过程的信息"></a>查看存储过程的信息</h3><p>—————内连接—————-<br>–语法一：<br><code>select i.stuno,stuname,writtenexam from stuinfo i inner join stumarks s on i.stuno=s.stuno;</code><br>–语法二：<br><code>select stuinfo.stuno,stuname,writtenexam from stuinfo,stumarks where stuinfo.stuno=stumarks.stuno;</code><br>–inner 可以省略<br><code>select i.stuno,stuname,writtenexam from stuinfo i join stumarks s on i.stuno=s.stuno;</code><br>————–左外连接,右外连接———-<br><code>select stuinfo.stuno,stuname,writtenexam,labexam from stuinfo left join stumarks on stuinfo.stuno=stumarks.stuno;</code></p>
<p><code>select stuinfo.stuno,stuname,writtenexam,labexam from stuinfo right join stumarks on stuinfo.stuno=stumarks.stuno;</code></p>
<p>————–自然连接—————<br>–自然内连接<br><code>select * from t1 natural join t2;</code><br>–自然左外连接<br><code>select * from t1 natural left join t2;</code><br>–自然右外连接<br><code>select * from t1 natural right join t2;</code></p>
<p>—————-using()——————-<br><code>select * from t1 inner join t2 using(id);</code><br><code>select * from t1 left join t2 using(id);</code></p>
<p>——————-子查询————————–<br>–查找80分的学生<br>–方法一<br><code>select stuname,writtenexam from stuinfo natural join stumarks where writtenexam=80;</code><br>–方法二<br><code>select * from stuinfo where stuno=(select stuno from stumarks where writtenexam=80);</code></p>
<p>–找出最高分<br><code>select * from stuinfo where stuno=(select stuno from stumarks where writtenexam=(select max(writtenexam) from stumarks));</code></p>
<p>–查找及格的学生<br><code>select * from stuinfo where stuno in (select stuno from stumarks where writtenexam&gt;=60);</code></p>
<p>–查找不及格的学生<br><code>select * from stuinfo where stuno not in (select stuno from stumarks where writtenexam&gt;=60);</code></p>
<p>–查找缺考的学生<br><code>select * from stuinfo where stuno in (select stuno)</code></p>
<p><code>select * from stuinfo where stuno in (select stuno from stuinfo natural left join stumarks where writtenexam is null);</code></p>
<p>–some</p>
<p><code>select * from stuinfo where stuno=some(select stuno from stumarks where writtenexam&gt;=60);</code></p>
<p>–exits<br><code>select * from stuinfo where exists (select * from stumarks where writtenexam&gt;=80)</code></p>
<p>–行子查询<br><code>select * from stu where (stusex,ch) in (select stusex,max(ch) from stu group by stusex);</code></p>
<p>–表子查询<br><code>select * from (select * from stu order by ch desc) as t group by stusex;</code></p>
<p>–示地区和每个地区参见考试的人数，并按人数降序排列<br><code>select stuaddress,count(writtenexam) c from stuinfo left join stumarks using(stuno) group by stuaddress order by c desc;</code></p>
<p>–显示有学生参见考试的地区<br><code>select stuaddress,count(writtenexam) c from stuinfo left join stumarks using(stuno) group by stuaddress having c&gt;0;</code></p>
<p><code>select distinct stuaddress from stuinfo natural right join stumarks where stuaddress is not null;</code></p>
<p>–显示男生和女生的人数<br><code>select stusex,count(*) from stuinfo where stusex=&#39;男&#39; union select stusex,count(*) from stuinfo where stusex=&#39;女&#39;;</code></p>
<p><code>select sum(stusex=&#39;男&#39;) 男,sum(stusex=&#39;女&#39;) 女 from stuinfo;</code></p>
<p>–显示每个地区的男生和女生，以及总人数<br><code>select stuaddress,count(*) 总人数,sum(stusex=&#39;男&#39;) 男,sum(stusex=&#39;女&#39;) 女 from stuinfo group by stuaddress;</code></p>
<p>———————–视图———————–</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div></pre></td><td class="code"><pre><div class="line">create view vw_stu</div><div class="line">as</div><div class="line">  select stuname,stusex,writtenexam from stuinfo inner join stumarks using (stuno);</div></pre></td></tr></table></figure>
<p><code>select * from (select * from stu order by ch desc) t group by stusex;</code></p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div></pre></td><td class="code"><pre><div class="line">create or replace algorithm=temptable view vw1</div><div class="line">as</div><div class="line">select * from stu order by ch desc;</div></pre></td></tr></table></figure>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div></pre></td><td class="code"><pre><div class="line">--</div><div class="line">create table temp(</div><div class="line">id int,</div><div class="line">name varchar(20),</div><div class="line">age tinyint,</div><div class="line">index ix_temp_name(name),</div><div class="line">key(age)</div><div class="line">)</div></pre></td></tr></table></figure>
<h3 id="多表查询分类-1"><a href="#多表查询分类-1" class="headerlink" title="多表查询分类"></a>多表查询分类</h3><p>将多个表的数据横向的联合起来。<br>1、    内连接<br>2、    外连接<br>a)    左外连接<br>b)    右外连接<br>3、    交叉连接<br>4、    自然连接</p>

          
        
      
    </div>

    <div>
      
    </div>

    <div>
      
    </div>

    <footer class="post-footer">
      

      

      
      
        <div class="post-eof"></div>
      
    </footer>
  </article>


    
      

  
  

  
  
  

  <article class="post post-type-normal " itemscope itemtype="http://schema.org/Article">

    
      <header class="post-header">

        
        
          <h1 class="post-title" itemprop="name headline">
            
            
              
                
                <a class="post-title-link" href="/20160901-1.html" itemprop="url">
                  Day9-MySQL 数据库设计及步骤
                </a>
              
            
          </h1>
        

        <div class="post-meta">
        
          <span class="post-time">
            <span class="post-meta-item-icon">
              <i class="fa fa-calendar-o"></i>
            </span>
            <span class="post-meta-item-text">发表于</span>
            <time itemprop="dateCreated" datetime="2016-09-01T17:18:51+08:00" content="2016-09-01">
              2016-09-01
            </time>
          </span>

          
            <span class="post-category" >
              &nbsp; | &nbsp;
              <span class="post-meta-item-icon">
                <i class="fa fa-folder-o"></i>
              </span>
              <span class="post-meta-item-text">分类于</span>
              
                <span itemprop="about" itemscope itemtype="https://schema.org/Thing">
                  <a href="/categories/MySQL/" itemprop="url" rel="index">
                    <span itemprop="name">MySQL</span>
                  </a>
                </span>

                
                

              
            </span>
          

          
            
              <span class="post-comments-count">
                &nbsp; | &nbsp;
                <a href="/20160901-1.html#comments" itemprop="discussionUrl">
                  <span class="post-comments-count ds-thread-count" data-thread-key="20160901-1.html" itemprop="commentsCount"></span>
                </a>
              </span>
            
          

          

          
          

          
        </div>
      </header>
    


    <div class="post-body" itemprop="articleBody">

      
      

      
        
          
            <h3 id="实体和实体之间的关系"><a href="#实体和实体之间的关系" class="headerlink" title="实体和实体之间的关系"></a>实体和实体之间的关系</h3><h4 id="一对多-1：N（多对一-N：1）"><a href="#一对多-1：N（多对一-N：1）" class="headerlink" title="一对多 1：N（多对一 N：1）"></a>一对多 1：N（多对一 N：1）</h4><p>主表中的一条记录对应从表中的多条记录</p>
<p>1、    主键和非主键之间的关系就能实现一对多。<br>2、    一对多和多对一是一样的。</p>
<blockquote>
<p>问题：几个一对多的关系？<br>1、    客户和订单<br>2、    省和市的关系<br>3、    商品类别和商品</p>
</blockquote>
<h4 id="一对一（1-1）"><a href="#一对一（1-1）" class="headerlink" title="一对一（1:1）"></a>一对一（1:1）</h4><p>主表中的一条记录对应从表中的一条记录</p>
<p><strong>主键和主键建关系就能实现一对一。</strong></p>
<blockquote>
<p>思考：一对一两个表完全可以用一个表实现，为什么还要分成两个表？<br>答：在字段数量很多情况下，数据量也就很大，每次查询都需要检索大量数据，这样效率低下。我们可以将所有字段分成两个部分，“常用字段”和“不常用字段”，这样对大部分查询者来说效率提高了。【表的垂直分割】</p>
</blockquote>
<h4 id="多对多（N：M）"><a href="#多对多（N：M）" class="headerlink" title="多对多（N：M）"></a>多对多（N：M）</h4><p>班级和讲师的关系<br>科目和学生的关系</p>
<blockquote>
<p>只要是多对多，必须有第三张关系表来保存关系</p>
</blockquote>
<h3 id="数据库设计的步骤"><a href="#数据库设计的步骤" class="headerlink" title="数据库设计的步骤"></a>数据库设计的步骤</h3><p>目的：<br>数据冗余、空间浪费—》节省数据的存储空间<br>内存空间浪费、数据不完整–》保证数据的完整性<br>数据更新与插入异常–》方便进行数据库应用系统的开发</p>
<p><strong>需求分析=&gt;建模=&gt;模型转换=&gt;规范化</strong></p>
<p>需求分析阶段：分析客户的业务和数据处理需求;<br>概要设计阶段：设计数据库的<strong>E-R模型图</strong>，确认需求信息的正确和完整;<br>详细设计阶段：将E-R图转换为多张表，进行逻辑设计，并应用数据库设计的三大范式进行审核;<br>代码编写阶段：选择具体数据库进行物理实现，并编写代码实现前端应用;<br>软件测试阶段：……<br>安装部署：……</p>
<h4 id="数据库设计具体步骤"><a href="#数据库设计具体步骤" class="headerlink" title="数据库设计具体步骤"></a>数据库设计具体步骤</h4><p>1、    收集信息：与该系统有关人员进行交流、坐谈，充分理解数据库需要完成的任务<br>2、    标识对象（实体－Entity）标识数据库要管理的关键对象或实体<br>3、    标识每个实体的属性（Attribute）<br>4、    标识对象之间的关系（Relationship）</p>
<p><strong>项目需求</strong><br>BBS论坛的基本功能：<br>1、<strong>用户</strong>注册和登录，后台数据库需要存放用户的注册信息和在线状态信息；<br>2、用户<strong>发贴</strong>，后台数据库需要存放贴子相关信息，如贴子内容、标题等；<br>3、用户可以对发帖进行回复【<strong>跟帖</strong>】；<br>4、论坛<strong>版块</strong>管理：后台数据库需要存放各个版块信息，如版主、版块名称、贴子数等；</p>
<p><strong>标识实体</strong><br>实体一般是名词：<br><strong>用户</strong>：论坛普通用户、各版块的版主。<br>用户发的<strong>主贴</strong><br>用户发的<strong>跟贴</strong>（回贴）<br><strong>版块</strong>：论坛的各个版块信息</p>
<p><strong>标识实体的属性</strong><br>论坛用户：<br>昵称、密码、邮件、生日、性别、等级、备注、注册日期、状态、积分<br>主贴：<br>发帖人、发帖表情、回复数量、标题、正文、发帖时间、点击数、状态<br>回帖：<br>帖子编号、回帖人、表情、标题、正文、回帖时间、点击数<br>板块：<br>板块名称、版主、本版格言、点击率、发帖数</p>
<p><strong>标识实体和实体之间的关系</strong><br>一个版块有多个主贴<br>一个主贴有多个跟帖<br>一个用户可以管理多个版块<br>一个用户可以发多个主贴<br>一个用户可以回复多个跟帖</p>
<h4 id="绘制E-R图"><a href="#绘制E-R图" class="headerlink" title="绘制E-R图"></a>绘制E-R图</h4><p>E-R（Entity－Relationship）实体关系图 </p>
<p>E-R图的语法<br>方形：实体，一般是名词<br>椭圆：属性，一般是名词<br>菱形：关系，一般是动词</p>
<p>将E-R图转成表<br>1、    实体转成表，属性转成字段<br>2、    如果没有合适的字段做主键，给表添加一个自动增长列做主键。</p>
<h3 id="数据规范化"><a href="#数据规范化" class="headerlink" title="数据规范化"></a>数据规范化</h3><p>Codd博士定义了6个范式来规范化数据库，范式由小到大来约束，范式越高冗余越小，但表的个数也越多。实验证明，三范式是性价比最高的</p>
<h4 id="第一范式确保每个字段不可再分"><a href="#第一范式确保每个字段不可再分" class="headerlink" title="第一范式确保每个字段不可再分"></a>第一范式确保每个字段不可再分</h4><p>说明：Address由country和city构成，将Address分成country和city。</p>
<blockquote>
<p>思考：地址包含省、市、县、地区是否需要拆分？<br>答：如果仅仅起地址的作用，不需要统计，可以不拆分；如果有按地区统计的功能需要拆分。<br>在实际项目中拆分!</p>
</blockquote>
<h4 id="第二范式：非键字段必须依赖于键字段"><a href="#第二范式：非键字段必须依赖于键字段" class="headerlink" title="第二范式：非键字段必须依赖于键字段"></a>第二范式：非键字段必须依赖于键字段</h4><p>一个表只能描述一件事<br>非键字段必须依赖于键字段</p>
<h4 id="第三范式：消除传递依赖"><a href="#第三范式：消除传递依赖" class="headerlink" title="第三范式：消除传递依赖"></a>第三范式：消除传递依赖</h4><p>在所有的非键字段中，不能有传递依赖</p>
<blockquote>
<p>例如在非主键中，“学号”和”姓名“有依赖，将”姓名“删除<br>例如顾客编号依赖于顾客姓名<br>总分数依赖于语文和数学的分数<br>在非主键字段中，要消除传递依赖</p>
<p>上面的设计不满足第三范式，但是高考分数表就是这样设计的，为什么？<br>答：高考分数峰值访问量非常大，这时候就是性能更重要。当性能和规范化冲突的时候，我们首选性能。这就是“反三范式”。</p>
</blockquote>
<h3 id="例题演练"><a href="#例题演练" class="headerlink" title="例题演练"></a>例题演练</h3><h4 id="需求"><a href="#需求" class="headerlink" title="需求"></a>需求</h4><p>假设某建筑公司要设计一个数据库。公司的业务规则概括说明如下：<br>1、公司承担多个工程项目，每一项工程有：工程号、工程名称、施工人员等<br>2、公司有多名职工，每一名职工有：职工号、姓名、性别、职务（工程师、技术员）等<br>3、公司按照工时和小时工资率支付工资，小时工资率由职工的职务决定（例如，技术员的小时工资率与工程师不同）</p>
<h4 id="设计异常"><a href="#设计异常" class="headerlink" title="设计异常"></a>设计异常</h4><p>1、    插入异常：一个职工入职，由于“工程名称”不能为空，所以必须虚拟一个工程名称。<br>2、    更新异常：“李思岐”从“技术员”升级“工程师”，结果“小时工资率”没有从60调整到65。<br>3、    删除异常：“李思岐”和“葛宇洪”离职，将二人删除，结果把“临江饭店”这个工程也删除了。</p>
<h4 id="优化表结构"><a href="#优化表结构" class="headerlink" title="优化表结构"></a>优化表结构</h4><p>工程表：工程号、项目名称<br>员工表：职工号、姓名、职务<br>职务表：职务、小时工资<br>工时表：工程号、职工号、工时</p>
<h3 id="查询语句"><a href="#查询语句" class="headerlink" title="查询语句"></a>查询语句</h3><p>语法：<code>select [选项] 字段名 from 表名 [where 条件] [group by 分组] [having 条件] [order by 排序] [limit 限制]</code></p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div></pre></td><td class="code"><pre><div class="line">mysql&gt; create table student(</div><div class="line">    -&gt; id int auto_increment primary key,</div><div class="line">    -&gt; name char(16) not null,</div><div class="line">    -&gt; `语文` int(5),</div><div class="line">    -&gt; `数学` int(5)</div><div class="line">    -&gt; )charset=utf8;</div><div class="line">Query OK, 0 rows affected (0.03 sec)</div><div class="line"></div><div class="line">mysql&gt; insert into student values(null,&apos;张宝&apos;,56,67),(null,&apos;李青霞&apos;,89,92),(null,&apos;兰丁丁&apos;,29,96);</div><div class="line">Query OK, 3 rows affected (0.01 sec)</div><div class="line">Records: 3  Duplicates: 0  Warnings: 0</div></pre></td></tr></table></figure>
<h4 id="字段表达式"><a href="#字段表达式" class="headerlink" title="字段表达式"></a>字段表达式</h4><figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div><div class="line">13</div><div class="line">14</div><div class="line">15</div><div class="line">16</div><div class="line">17</div><div class="line">18</div><div class="line">19</div><div class="line">20</div><div class="line">21</div><div class="line">22</div><div class="line">23</div><div class="line">24</div><div class="line">25</div></pre></td><td class="code"><pre><div class="line">mysql&gt; select 20;</div><div class="line">+----+</div><div class="line">| 20 |</div><div class="line">+----+</div><div class="line">| 20 |</div><div class="line">+----+</div><div class="line">1 row in set (0.00 sec)</div><div class="line"></div><div class="line">mysql&gt; select 20*20;</div><div class="line">+-------+</div><div class="line">| 20*20 |</div><div class="line">+-------+</div><div class="line">|   400 |</div><div class="line">+-------+</div><div class="line">1 row in set (0.01 sec)</div><div class="line"></div><div class="line">mysql&gt; select id,name,`语文`,`数学`,(`语文`+`数学`) from student;</div><div class="line">+----+-----------+--------+--------+---------------------+</div><div class="line">| id | name      | 语文   | 数学   | (`语文`+`数学`)     |</div><div class="line">+----+-----------+--------+--------+---------------------+</div><div class="line">|  1 | 张宝      |     56 |     67 |                 123 |</div><div class="line">|  2 | 李青霞    |     89 |     92 |                 181 |</div><div class="line">|  3 | 兰丁丁    |     29 |     96 |                 125 |</div><div class="line">+----+-----------+--------+--------+---------------------+</div><div class="line">3 rows in set (0.01 sec)</div></pre></td></tr></table></figure>
<p>时间戳</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div></pre></td><td class="code"><pre><div class="line">mysql&gt; select unix_timestamp();</div><div class="line">+------------------+</div><div class="line">| unix_timestamp() |</div><div class="line">+------------------+</div><div class="line">|       1472347068 |</div><div class="line">+------------------+</div><div class="line">1 row in set (0.01 sec)</div></pre></td></tr></table></figure>
<p>随机数</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div></pre></td><td class="code"><pre><div class="line">mysql&gt; select rand();</div><div class="line">+--------------------+</div><div class="line">| rand()             |</div><div class="line">+--------------------+</div><div class="line">| 0.8859497913028578 |</div><div class="line">+--------------------+</div><div class="line">1 row in set (0.00 sec)</div></pre></td></tr></table></figure>
<p>通过as给字段取别名</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div></pre></td><td class="code"><pre><div class="line">mysql&gt; select unix_timestamp() as &apos;时间戳&apos;,rand() as &apos;随机数&apos;;</div><div class="line">+------------+--------------------+</div><div class="line">| 时间戳     | 随机数             |</div><div class="line">+------------+--------------------+</div><div class="line">| 1472347205 | 0.8530956766131294 |</div><div class="line">+------------+--------------------+</div><div class="line">1 row in set (0.01 sec)</div></pre></td></tr></table></figure>
<blockquote>
<p>as可以省略</p>
</blockquote>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div></pre></td><td class="code"><pre><div class="line">mysql&gt; select unix_timestamp() &apos;时间戳&apos;,rand() &apos;随机数&apos;;</div><div class="line">+------------+--------------------+</div><div class="line">| 时间戳     | 随机数             |</div><div class="line">+------------+--------------------+</div><div class="line">| 1472347268 | 0.6076290398907187 |</div><div class="line">+------------+--------------------+</div><div class="line">1 row in set (0.00 sec)</div></pre></td></tr></table></figure>
<h4 id="from-子句"><a href="#from-子句" class="headerlink" title="from 子句"></a>from 子句</h4><p>from：来自，from后面跟的是数据源。数据源可以有多个。返回笛卡尔积(排列组合)</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div><div class="line">13</div><div class="line">14</div><div class="line">15</div><div class="line">16</div><div class="line">17</div><div class="line">18</div><div class="line">19</div><div class="line">20</div><div class="line">21</div><div class="line">22</div><div class="line">23</div></pre></td><td class="code"><pre><div class="line">mysql&gt; create table teacher(</div><div class="line">    -&gt; id int auto_increment primary key,</div><div class="line">    -&gt; name char(16) not null,</div><div class="line">    -&gt; `评分` int(3) zerofill</div><div class="line">    -&gt; )charset=utf8;</div><div class="line">Query OK, 0 rows affected (0.03 sec)</div><div class="line"></div><div class="line">mysql&gt; insert into teacher values (null,&apos;贾老师&apos;,98),(null,&apos;杜老师&apos;,59);</div><div class="line">Query OK, 2 rows affected (0.01 sec)</div><div class="line">Records: 2  Duplicates: 0  Warnings: 0</div><div class="line"></div><div class="line">mysql&gt; select * from student,teacher;</div><div class="line">+----+-----------+--------+--------+----+-----------+--------+</div><div class="line">| id | name      | 语文   | 数学   | id | name      | 评分   |</div><div class="line">+----+-----------+--------+--------+----+-----------+--------+</div><div class="line">|  1 | 张宝      |     56 |     67 |  1 | 贾老师    |    098 |</div><div class="line">|  1 | 张宝      |     56 |     67 |  2 | 杜老师    |    059 |</div><div class="line">|  2 | 李青霞    |     89 |     92 |  1 | 贾老师    |    098 |</div><div class="line">|  2 | 李青霞    |     89 |     92 |  2 | 杜老师    |    059 |</div><div class="line">|  3 | 兰丁丁    |     29 |     96 |  1 | 贾老师    |    098 |</div><div class="line">|  3 | 兰丁丁    |     29 |     96 |  2 | 杜老师    |    059 |</div><div class="line">+----+-----------+--------+--------+----+-----------+--------+</div><div class="line">6 rows in set (0.01 sec)</div></pre></td></tr></table></figure>
<blockquote>
<p>返回笛卡尔积</p>
</blockquote>
<p>可以通过as给表取别名(给表取别名，as可以省略)</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div></pre></td><td class="code"><pre><div class="line">mysql&gt; select i.name,m.name from student as i,teacher as m;</div><div class="line">+-----------+-----------+</div><div class="line">| name      | name      |</div><div class="line">+-----------+-----------+</div><div class="line">| 张宝      | 贾老师    |</div><div class="line">| 张宝      | 杜老师    |</div><div class="line">| 李青霞    | 贾老师    |</div><div class="line">| 李青霞    | 杜老师    |</div><div class="line">| 兰丁丁    | 贾老师    |</div><div class="line">| 兰丁丁    | 杜老师    |</div><div class="line">+-----------+-----------+</div><div class="line">6 rows in set (0.01 sec)</div></pre></td></tr></table></figure>
<h4 id="dual表（伪表）"><a href="#dual表（伪表）" class="headerlink" title="dual表（伪表）"></a>dual表（伪表）</h4><p>dual表是一个伪表。在有些特定情况下，没有具体的表的参与，但是为了保证select语句的完整又必须要一个表名，这时候就使用伪表。<br>dual表是用来保证select语句的完整性</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div></pre></td><td class="code"><pre><div class="line">mysql&gt; select 20*30 as `积` from dual;</div><div class="line">+-----+</div><div class="line">| 积  |</div><div class="line">+-----+</div><div class="line">| 600 |</div><div class="line">+-----+</div><div class="line">1 row in set (0.00 sec)</div></pre></td></tr></table></figure>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div></pre></td><td class="code"><pre><div class="line">mysql&gt; select * from student where `语文`&gt;59;</div><div class="line">mysql&gt; select * from student where 1;	--返回所有数据</div><div class="line">mysql&gt; select * from student where 0;	--返回空</div></pre></td></tr></table></figure>
<blockquote>
<p>思考：数据库中的表是一个二维表，返回的结果是一张二维表，既然能在数据库的二维表中进行查询，能否在结果集的二维表上继续进行查询？<br>答：可以，使用having条件就是在结果集上继续进行筛选。</p>
</blockquote>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div></pre></td><td class="code"><pre><div class="line">mysql&gt; select * from student where name in (&apos;兰丁丁&apos;,&apos;张宝&apos;);</div><div class="line">+----+-----------+--------+--------+</div><div class="line">| id | name      | 语文   | 数学   |</div><div class="line">+----+-----------+--------+--------+</div><div class="line">|  1 | 张宝      |     56 |     67 |</div><div class="line">|  3 | 兰丁丁    |     29 |     96 |</div><div class="line">+----+-----------+--------+--------+</div><div class="line">2 rows in set (0.02 sec)</div></pre></td></tr></table></figure>
<blockquote>
<p>查询名字是兰丁丁和张宝的信息</p>
</blockquote>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div></pre></td><td class="code"><pre><div class="line">mysql&gt; select * from teacher where name not in (&apos;杜老师&apos;);</div><div class="line">+----+-----------+--------+</div><div class="line">| id | name      | 评分   |</div><div class="line">+----+-----------+--------+</div><div class="line">|  1 | 贾老师    |    098 |</div><div class="line">+----+-----------+--------+</div><div class="line">1 row in set (0.00 sec)</div></pre></td></tr></table></figure>
<blockquote>
<p>查询不包含杜老师的信息</p>
</blockquote>
<p>in：在…里面<br>not in：不在…里面</p>
<h4 id="between…and-not-between…and"><a href="#between…and-not-between…and" class="headerlink" title="between…and|not between…and"></a>between…and|not between…and</h4><p>between…and：在…之间</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div></pre></td><td class="code"><pre><div class="line">mysql&gt; select * from student where id between 2 and 3;</div><div class="line">+----+-----------+--------+--------+</div><div class="line">| id | name      | 语文   | 数学   |</div><div class="line">+----+-----------+--------+--------+</div><div class="line">|  2 | 李青霞    |     89 |     92 |</div><div class="line">|  3 | 兰丁丁    |     29 |     96 |</div><div class="line">+----+-----------+--------+--------+</div><div class="line">2 rows in set (0.00 sec)</div></pre></td></tr></table></figure>
<blockquote>
<p>id在2和之间包含2和3</p>
</blockquote>
<p>not between …and 不在…之间</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div></pre></td><td class="code"><pre><div class="line">mysql&gt; select * from student where id not between 2 and 2;</div><div class="line">+----+-----------+--------+--------+</div><div class="line">| id | name      | 语文   | 数学   |</div><div class="line">+----+-----------+--------+--------+</div><div class="line">|  1 | 张宝      |     56 |     67 |</div><div class="line">|  3 | 兰丁丁    |     29 |     96 |</div><div class="line">+----+-----------+--------+--------+</div><div class="line">2 rows in set (0.01 sec)</div></pre></td></tr></table></figure>
<blockquote>
<p>id不在2和2之间</p>
</blockquote>
<h4 id="is-null-is-not-null"><a href="#is-null-is-not-null" class="headerlink" title="is null | is not null"></a>is null | is not null</h4><figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div></pre></td><td class="code"><pre><div class="line">mysql&gt; insert into student values (null,&apos;赵哥&apos;,null,89);</div><div class="line">Query OK, 1 row affected (0.01 sec)</div><div class="line"></div><div class="line"> +----+--------+--------+--------+</div><div class="line">| id | name   | 语文   | 数学   |</div><div class="line">+----+--------+--------+--------+</div><div class="line">|  4 | 赵哥   |   NULL |     89 |</div><div class="line">+----+--------+--------+--------+</div><div class="line">1 row in set (0.00 sec)</div></pre></td></tr></table></figure>
<blockquote>
<p>查询语文或数学为空的信息；</p>
</blockquote>
<p><strong>查询一个为空的字段不能用等于，必须用is null</strong></p>
<h4 id="标量子查询"><a href="#标量子查询" class="headerlink" title="标量子查询"></a>标量子查询</h4><p>= ：结果就是一个字<br><code>select * from stu where id=(select id from class where name=&#39;jack&#39;);</code></p>
<h4 id="列子查询"><a href="#列子查询" class="headerlink" title="列子查询"></a>列子查询</h4><p>in ：一个字段对应多个值<br><code>select * from stu where id in(select id from class where name in(&#39;php&#39;,&#39;java&#39;));</code></p>
<h4 id="行子查询"><a href="#行子查询" class="headerlink" title="行子查询"></a>行子查询</h4><p>= ：n个字段名对n个值<br><code>select * from stu where(id,name)=(select id,name from class where name=&#39;php&#39;);</code></p>
<h4 id="聚合函数"><a href="#聚合函数" class="headerlink" title="聚合函数"></a>聚合函数</h4><table>
<thead>
<tr>
<th>聚合函数</th>
<th style="text-align:center">描述</th>
</tr>
</thead>
<tbody>
<tr>
<td>sum()</td>
<td style="text-align:center">求和</td>
</tr>
<tr>
<td>avg()</td>
<td style="text-align:center">求平均值</td>
</tr>
<tr>
<td>max()</td>
<td style="text-align:center">求最大值</td>
</tr>
<tr>
<td>min()</td>
<td style="text-align:center">求最小值</td>
</tr>
<tr>
<td>count()</td>
<td style="text-align:center">求记录数</td>
</tr>
</tbody>
</table>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div></pre></td><td class="code"><pre><div class="line">mysql&gt; select sum(`语文`) as `语文总分`,avg(`语文`) as `语文平均分`,max(`语文`) as `语文最高分`,min(`语文`) as `语文最低分`,count(*) as `总人数` from student;</div><div class="line">+--------------+-----------------+-----------------+-----------------+-----------+</div><div class="line">| 语文总分     | 语文平均分      | 语文最高分      | 语文最低分      | 总人数    |</div><div class="line">+--------------+-----------------+-----------------+-----------------+-----------+</div><div class="line">|          174 |         58.0000 |              89 |              29 |         4 |</div><div class="line">+--------------+-----------------+-----------------+-----------------+-----------+</div><div class="line">1 row in set (0.01 sec)</div></pre></td></tr></table></figure>
<h4 id="通配符"><a href="#通配符" class="headerlink" title="通配符"></a>通配符</h4><table>
<thead>
<tr>
<th>通配符</th>
<th style="text-align:center">描述</th>
</tr>
</thead>
<tbody>
<tr>
<td>_  [下划线]</td>
<td style="text-align:center">表示任意一个字符</td>
</tr>
<tr>
<td>%</td>
<td style="text-align:center">表示任意字符</td>
</tr>
</tbody>
</table>
<blockquote>
<p>练习<br>1、满足“T_m”的有（A、C）<br>A：Tom        B：Toom        C：Tam        D：Tm    E：Tmo<br>2、满足“T<em>m</em>”的有（B、C）<br>A:Tmom    B:Tmmm    C:T1m2    D:Tmm    E:Tm<br>3、满足“张%”的是（A、B、C、D）<br>A:张三    B：张三丰    C：张牙舞爪        D：张        E：小张<br>4、满足“%诺基亚%”的是（A、B、C、D）<br>A：诺基亚2100   B：2100诺基亚    C：把我的诺基亚拿过来   D：诺基亚</p>
</blockquote>
<p><strong>如果要匹配6个字符，且已ing结尾则使用“___ing” 3个下划线</strong></p>
<p>使用“\”转移特殊字符 “\%”;</p>
<h4 id="模糊查询（like）"><a href="#模糊查询（like）" class="headerlink" title="模糊查询（like）"></a>模糊查询（like）</h4><figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div></pre></td><td class="code"><pre><div class="line">mysql&gt; select * from student where name like &apos;兰%&apos;;</div><div class="line">+----+-----------+--------+--------+</div><div class="line">| id | name      | 语文   | 数学   |</div><div class="line">+----+-----------+--------+--------+</div><div class="line">|  3 | 兰丁丁    |     29 |     96 |</div><div class="line">+----+-----------+--------+--------+</div><div class="line">1 row in set (0.01 sec)</div></pre></td></tr></table></figure>
<blockquote>
<p>查询兰姓小伙伴</p>
</blockquote>
<h4 id="group-by-【分组查询】"><a href="#group-by-【分组查询】" class="headerlink" title="group by 【分组查询】"></a>group by 【分组查询】</h4><p>将查询的结果分组，分组查询目的在于统计数据</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div></pre></td><td class="code"><pre><div class="line">mysql&gt; select id,name,sex,avg(`数学`) as `数学平均分数` from student group by sex;</div><div class="line">+----+-----------+------+--------------------+</div><div class="line">| id | name      | sex  | 数学平均分数       |</div><div class="line">+----+-----------+------+--------------------+</div><div class="line">|  2 | 李青霞    | 女   |            92.0000 |</div><div class="line">|  1 | 张宝      | 男   |            84.0000 |</div><div class="line">+----+-----------+------+--------------------+</div><div class="line">2 rows in set (0.01 sec)</div></pre></td></tr></table></figure>
<blockquote>
<p>分组查询中，字段有普通字段会怎么样？<br>只显示分组后第一条记录的字段值</p>
<p>注意内容插入的时候使用引号 ‘’ ，查询字段信息时使用 `` ;</p>
</blockquote>
<p>通过group_concat()函数将同一组的值连接起来显示</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div></pre></td><td class="code"><pre><div class="line">mysql&gt; select group_concat(id),group_concat(name),sex,avg(`语文`) as `语文平均分`,avg(`数学`) as `数学平均分` from student group by sex;</div><div class="line">+------------------+-------------------------+------+-----------------+-----------------+</div><div class="line">| group_concat(id) | group_concat(name)      | sex  | 语文平均分      | 数学平均分      |</div><div class="line">+------------------+-------------------------+------+-----------------+-----------------+</div><div class="line">| 2                | 李青霞                  | 女   |         89.0000 |         92.0000 |</div><div class="line">| 1,3,4            | 张宝,兰丁丁,赵哥        | 男   |         42.5000 |         84.0000 |</div><div class="line">+------------------+-------------------------+------+-----------------+-----------------+</div><div class="line">2 rows in set (0.01 sec)</div></pre></td></tr></table></figure>
<p>实现分组后按降序排列</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div></pre></td><td class="code"><pre><div class="line">mysql&gt; select * from student group by `语文` desc;</div><div class="line">+----+-----------+------+--------+--------+</div><div class="line">| id | name      | sex  | 语文   | 数学   |</div><div class="line">+----+-----------+------+--------+--------+</div><div class="line">|  2 | 李青霞    | 女   |     89 |     92 |</div><div class="line">|  1 | 张宝      | 男   |     56 |     67 |</div><div class="line">|  3 | 兰丁丁    | 男   |     29 |     96 |</div><div class="line">|  4 | 赵哥      | 男   |   NULL |     89 |</div><div class="line">+----+-----------+------+--------+--------+</div><div class="line">4 rows in set (0.00 sec)</div></pre></td></tr></table></figure>
<blockquote>
<p>分组后降序排列</p>
</blockquote>
<p>多列分组(注意：聚合函数*号的位置-&gt;count(*))</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div></pre></td><td class="code"><pre><div class="line">mysql&gt; select group_concat(name),sex,count(*) from student group by sex;</div><div class="line">+-------------------------+------+----------+</div><div class="line">| group_concat(name)      | sex  | count(*) |</div><div class="line">+-------------------------+------+----------+</div><div class="line">| 李青霞                  | 女   |        1 |</div><div class="line">| 张宝,兰丁丁,赵哥        | 男   |        3 |</div><div class="line">+-------------------------+------+----------+</div><div class="line">2 rows in set (0.01 sec)</div></pre></td></tr></table></figure>
<h4 id="回溯统计【with-rollup】"><a href="#回溯统计【with-rollup】" class="headerlink" title="回溯统计【with rollup】"></a>回溯统计【with rollup】</h4><p>在分组统计的基础上再进行相同的统计</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div><div class="line">13</div><div class="line">14</div><div class="line">15</div><div class="line">16</div><div class="line">17</div><div class="line">18</div><div class="line">19</div><div class="line">20</div><div class="line">21</div><div class="line">22</div><div class="line">23</div><div class="line">24</div><div class="line">25</div></pre></td><td class="code"><pre><div class="line">mysql&gt; update student set sex=null where id=3;</div><div class="line">Query OK, 1 row affected (0.01 sec)</div><div class="line">Rows matched: 1  Changed: 1  Warnings: 0</div><div class="line"></div><div class="line">mysql&gt; select * from student;</div><div class="line">+----+-----------+------+--------+--------+</div><div class="line">| id | name      | sex  | 语文   | 数学   |</div><div class="line">+----+-----------+------+--------+--------+</div><div class="line">|  1 | 张宝      | 男   |     56 |     67 |</div><div class="line">|  2 | 李青霞    | 女   |     89 |     92 |</div><div class="line">|  3 | 兰丁丁    | NULL |     29 |     96 |</div><div class="line">|  4 | 赵哥      | 男   |   NULL |     89 |</div><div class="line">+----+-----------+------+--------+--------+</div><div class="line">4 rows in set (0.00 sec)</div><div class="line"></div><div class="line">mysql&gt; select group_concat(name),sex,count(*) from student group by sex with rollup;</div><div class="line">+-----------------------------------+------+----------+</div><div class="line">| group_concat(name)                | sex  | count(*) |</div><div class="line">+-----------------------------------+------+----------+</div><div class="line">| 兰丁丁                            | NULL |        1 |</div><div class="line">| 李青霞                            | 女   |        1 |</div><div class="line">| 张宝,赵哥                         | 男   |        2 |</div><div class="line">| 兰丁丁,李青霞,张宝,赵哥           | NULL |        4 |</div><div class="line">+-----------------------------------+------+----------+</div><div class="line">4 rows in set (0.00 sec)</div></pre></td></tr></table></figure>
<blockquote>
<p>在分组统计的基础上再进行相同的统计</p>
</blockquote>
<h4 id="having条件"><a href="#having条件" class="headerlink" title="having条件"></a>having条件</h4><p><strong>having和where的区别：</strong><br><strong>where</strong>是对原始数据进行筛选，<strong>having</strong>是对记录集(结果集)进行筛选。</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div></pre></td><td class="code"><pre><div class="line">mysql&gt; select * from student having `语文`&lt;59;</div><div class="line">+----+-----------+------+--------+--------+</div><div class="line">| id | name      | sex  | 语文   | 数学   |</div><div class="line">+----+-----------+------+--------+--------+</div><div class="line">|  1 | 张宝      | 男   |     56 |     67 |</div><div class="line">|  3 | 兰丁丁    | NULL |     29 |     96 |</div><div class="line">+----+-----------+------+--------+--------+</div><div class="line">2 rows in set (0.00 sec)</div></pre></td></tr></table></figure>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div></pre></td><td class="code"><pre><div class="line">mysql&gt; select name from student where `数学`&gt;80;</div><div class="line">+-----------+</div><div class="line">| name      |</div><div class="line">+-----------+</div><div class="line">| 李青霞    |</div><div class="line">| 兰丁丁    |</div><div class="line">| 赵哥      |</div><div class="line">+-----------+</div><div class="line">3 rows in set (0.00 sec)</div><div class="line"></div><div class="line">mysql&gt; select name from student having `数学`&gt;80;</div><div class="line">ERROR 1054 (42S22): Unknown column &apos;数学&apos; in &apos;having clause&apos;</div></pre></td></tr></table></figure>
<blockquote>
<p>因为在表中查询，表中有 `数学` ，所以where可以查到结果，反之having是查询不到结果的；</p>
</blockquote>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div></pre></td><td class="code"><pre><div class="line">mysql&gt; select group_concat(name),sex,count(*) as total from student group by sex having total&gt;1; </div><div class="line">+--------------------+------+-------+</div><div class="line">| group_concat(name) | sex  | total |</div><div class="line">+--------------------+------+-------+</div><div class="line">| 张宝,赵哥          | 男   |     2 |</div><div class="line">+--------------------+------+-------+</div><div class="line">1 row in set (0.00 sec)</div></pre></td></tr></table></figure>
<blockquote>
<p>对select出的数据，使用having进行条件设置</p>
</blockquote>
<p>####【where子句】<br>    主要是用于根据一个条件表达式从硬盘上将数据读到内存中时筛选记录。<br>既然where根据条件表达式进行记录的筛选，where的运算符<br>=        判断两个值是否相等<br>=        判断n个字段与n个值是否相等</p>
<h4 id="order-by排序"><a href="#order-by排序" class="headerlink" title="order by排序"></a>order by排序</h4><p><strong>asc：升序【默认】</strong><br><strong>desc：降序</strong></p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div></pre></td><td class="code"><pre><div class="line">mysql&gt; select * from student order by \`语文`;			--按语文升序</div><div class="line">mysql&gt; select * from student order by \`语文` asc;		--按语文升序</div><div class="line">mysql&gt; select * from student order by \`语文` desc;		--按语文降序</div></pre></td></tr></table></figure>
<h4 id="limit"><a href="#limit" class="headerlink" title="limit"></a>limit</h4><p><code>语法：limit 起始位置，显示长度</code><br>起始位置可以省略，默认是从0开始</p>
<p>找出班级总分前两名</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div></pre></td><td class="code"><pre><div class="line">mysql&gt; select name,(`语文`+`数学`) as total from student order by (`语文`+`数学`) desc limit 2;</div><div class="line">+-----------+-------+</div><div class="line">| name      | total |</div><div class="line">+-----------+-------+</div><div class="line">| 李青霞    |   181 |</div><div class="line">| 兰丁丁    |   125 |</div><div class="line">+-----------+-------+</div><div class="line">2 rows in set (0.00 sec)</div></pre></td></tr></table></figure>
<h4 id="查询语句中的选项"><a href="#查询语句中的选项" class="headerlink" title="查询语句中的选项"></a>查询语句中的选项</h4><p>查询语句中的选项有两个：<br>1、    all：显示所有数据 【默认】<br>2、    distinct：去除结果集中重复的数据</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div><div class="line">13</div><div class="line">14</div><div class="line">15</div><div class="line">16</div><div class="line">17</div><div class="line">18</div><div class="line">19</div><div class="line">20</div></pre></td><td class="code"><pre><div class="line">mysql&gt; select all sex from student;</div><div class="line">+------+</div><div class="line">| sex  |</div><div class="line">+------+</div><div class="line">| 男   |</div><div class="line">| 女   |</div><div class="line">| NULL |</div><div class="line">| 男   |</div><div class="line">+------+</div><div class="line">4 rows in set (0.00 sec)</div><div class="line"></div><div class="line">mysql&gt; select distinct sex from student;</div><div class="line">+------+</div><div class="line">| sex  |</div><div class="line">+------+</div><div class="line">| 男   |</div><div class="line">| 女   |</div><div class="line">| NULL |</div><div class="line">+------+</div><div class="line">3 rows in set (0.01 sec)</div></pre></td></tr></table></figure>
<h3 id="insert…select…"><a href="#insert…select…" class="headerlink" title="insert…select…"></a>insert…select…</h3><p>选择一个表的数据插入到另外的表中</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div><div class="line">13</div><div class="line">14</div><div class="line">15</div></pre></td><td class="code"><pre><div class="line">mysql&gt; create table stu_boy like student;</div><div class="line">Query OK, 0 rows affected (0.03 sec)</div><div class="line"></div><div class="line">mysql&gt; insert into stu_boy select * from student where sex=&apos;男&apos;;</div><div class="line">Query OK, 2 rows affected (0.02 sec)</div><div class="line">Records: 2  Duplicates: 0  Warnings: 0</div><div class="line"></div><div class="line">mysql&gt; select * from stu_boy;</div><div class="line">+----+--------+------+--------+--------+</div><div class="line">| id | name   | sex  | 语文   | 数学   |</div><div class="line">+----+--------+------+--------+--------+</div><div class="line">|  1 | 张宝   | 男   |     56 |     67 |</div><div class="line">|  4 | 赵哥   | 男   |   NULL |     89 |</div><div class="line">+----+--------+------+--------+--------+</div><div class="line">2 rows in set (0.00 sec)</div></pre></td></tr></table></figure>
<h3 id="on-duplicate-key-update"><a href="#on-duplicate-key-update" class="headerlink" title="on duplicate key update"></a>on duplicate key update</h3><p>在插入数据的时候，如果插入的数据不满足主键约束或唯一约束则执行更新操作。<br>测试数据</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div></pre></td><td class="code"><pre><div class="line">1	–创建表</div><div class="line">2	create table emp(</div><div class="line">3	id int primary key,</div><div class="line">4	name varchar(20) unique</div><div class="line">5	);</div><div class="line">6	– 插入测试数据</div><div class="line">7	insert into emp values (1,&apos;tom&apos;);</div></pre></td></tr></table></figure>
<p><code>mysql&gt;insert into emp values (1,&#39;berry&#39;) on duplicate key update name=&#39;berry&#39;</code></p>
<blockquote>
<p>编号1和主键冲突，将name改成berry</p>
</blockquote>
<p><code>mysql&gt;insert into emp values (2,&#39;berry&#39;) on duplicate key update id=2;</code></p>
<blockquote>
<p>berry 和唯一键冲突，将id改为2</p>
</blockquote>
<h3 id="union"><a href="#union" class="headerlink" title="union"></a>union</h3><p>作用：将多个select语句结果集纵向联合起来<br>语法：<code>select 语句 union [选项] select 语句 union [选项] select 语句</code></p>
<p>例题：查找上海的男生和北京的女生</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div></pre></td><td class="code"><pre><div class="line">--方法一：</div><div class="line">mysql&gt; select * from stu where (stuaddress=&apos;上海&apos; and stusex=&apos;男&apos;) or (stuaddress=&apos;北京&apos; and stusex=&apos;女&apos;);</div><div class="line"></div><div class="line">--方法二：</div><div class="line">mysql&gt; select * from stu where (stuaddress=&apos;上海&apos; and stusex=&apos;男&apos;) union select * from stu where (stuaddress=&apos;北京&apos; and stusex=&apos;女&apos;);</div></pre></td></tr></table></figure>
<p>例题：联合多个表的数据<br><code>mysql&gt; select stuname from stu union select name from stu_info;</code></p>
<p>例题：将多个表的数据插入到新表中<br><code>mysql&gt; create table stu2 like stu;        --创建和stu一样的表结构</code></p>
<p>–将上海的男生和北京的女生插入到stu2表中</p>
<p><code>insert into stu2 select * from stu where (stuaddress=&#39;上海&#39; and stusex=&#39;男&#39;) union select * from stu where (stuaddress=&#39;北京&#39; and stusex=&#39;女&#39;);</code></p>
<h4 id="union的选项"><a href="#union的选项" class="headerlink" title="union的选项"></a>union的选项</h4><p>union的选项有两个<br>1、    all：显示所有数据<br>2、    distinct：去除重复的数据【默认】</p>
<blockquote>
<p>默认情况下，取出重复的记录<br>union all 显示联合的数据，不去重</p>
</blockquote>
<h4 id="union的注意事项"><a href="#union的注意事项" class="headerlink" title="union的注意事项"></a>union的注意事项</h4><ul>
<li>union两边的select语句的字段个数必须一致</li>
</ul>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line">mysql&gt; select name from student union select name,sex from stu_boy;</div><div class="line">ERROR 1222 (21000): The used SELECT statements have a different number of columns</div></pre></td></tr></table></figure>
<ul>
<li>union两边的select语句的字段名可以不一致，最终按第一个select语句的字段名。</li>
</ul>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div></pre></td><td class="code"><pre><div class="line">mysql&gt; select id,name from student union select name,sex from stu_boy;</div><div class="line">+--------+-----------+</div><div class="line">| id     | name      |</div><div class="line">+--------+-----------+</div><div class="line">| 1      | 张宝      |</div><div class="line">| 2      | 李青霞    |</div><div class="line">| 3      | 兰丁丁    |</div><div class="line">| 4      | 赵哥      |</div><div class="line">| 张宝   | 男        |</div><div class="line">| 赵哥   | 男        |</div><div class="line">+--------+-----------+</div><div class="line">6 rows in set (0.00 sec)</div></pre></td></tr></table></figure>
<ul>
<li>union两边的select语句中的数据类型可以不一致。</li>
</ul>
<h4 id="union和order-by一起使用"><a href="#union和order-by一起使用" class="headerlink" title="union和order by一起使用"></a>union和order by一起使用</h4><p>1、    每条select语句必须用括号括起来。<br>2、    每条select语句的order by必须配合limit才能生效。</p>
<p>例题：男生按数学的降序，女生按语文的升序排列</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div></pre></td><td class="code"><pre><div class="line">mysql&gt; (select * from student where sex=&apos;男&apos; order by 数学 desc limit 999) union (select * from student where sex=&apos;女&apos; order by 语文 asc limit 999);</div><div class="line">+----+-----------+------+--------+--------+</div><div class="line">| id | name      | sex  | 语文   | 数学   |</div><div class="line">+----+-----------+------+--------+--------+</div><div class="line">|  4 | 赵哥      | 男   |   NULL |     89 |</div><div class="line">|  1 | 张宝      | 男   |     56 |     67 |</div><div class="line">|  2 | 李青霞    | 女   |     89 |     92 |</div><div class="line">+----+-----------+------+--------+--------+</div><div class="line">3 rows in set (0.00 sec)</div></pre></td></tr></table></figure>

          
        
      
    </div>

    <div>
      
    </div>

    <div>
      
    </div>

    <footer class="post-footer">
      

      

      
      
        <div class="post-eof"></div>
      
    </footer>
  </article>


    
  </section>

  
  <nav class="pagination">
    <a class="extend prev" rel="prev" href="/page/7/"><i class="fa fa-angle-left"></i></a><a class="page-number" href="/">1</a><span class="space">&hellip;</span><a class="page-number" href="/page/7/">7</a><span class="page-number current">8</span><a class="page-number" href="/page/9/">9</a><span class="space">&hellip;</span><a class="page-number" href="/page/14/">14</a><a class="extend next" rel="next" href="/page/9/"><i class="fa fa-angle-right"></i></a>
  </nav>



          </div>
          


          

        </div>
        
          
  
  <div class="sidebar-toggle">
    <div class="sidebar-toggle-line-wrap">
      <span class="sidebar-toggle-line sidebar-toggle-line-first"></span>
      <span class="sidebar-toggle-line sidebar-toggle-line-middle"></span>
      <span class="sidebar-toggle-line sidebar-toggle-line-last"></span>
    </div>
  </div>

  <aside id="sidebar" class="sidebar">
    <div class="sidebar-inner">

      

      

      <section class="site-overview sidebar-panel  sidebar-panel-active ">
        <div class="site-author motion-element" itemprop="author" itemscope itemtype="http://schema.org/Person">
          <img class="site-author-image" itemprop="image"
               src="/images/avatar.png"
               alt="uiste" />
          <p class="site-author-name" itemprop="name">uiste</p>
          <p class="site-description motion-element" itemprop="description">Stay Hungry，Stay Foolish</p>
        </div>
        <nav class="site-state motion-element">
          <div class="site-state-item site-state-posts">
            <a href="/archives">
              <span class="site-state-item-count">70</span>
              <span class="site-state-item-name">日志</span>
            </a>
          </div>

          
            <div class="site-state-item site-state-categories">
              
                <span class="site-state-item-count">13</span>
                <span class="site-state-item-name">分类</span>
              
            </div>
          

          
            <div class="site-state-item site-state-tags">
              <a href="/tags">
                <span class="site-state-item-count">55</span>
                <span class="site-state-item-name">标签</span>
              </a>
            </div>
          

        </nav>

        
          <div class="feed-link motion-element">
            <a href="/atom.xml" rel="alternate">
              <i class="fa fa-rss"></i>
              RSS
            </a>
          </div>
        

        <div class="links-of-author motion-element">
          
            
              <span class="links-of-author-item">
                <a href="https://github.com/uiste" target="_blank" rel="external nofollow" title="GitHub">
                  
                    <i class="fa fa-fw fa-github"></i>
                  
                  GitHub
                </a>
              </span>
            
              <span class="links-of-author-item">
                <a href="http://weibo.com/uiste" target="_blank" rel="external nofollow" title="微博">
                  
                    <i class="fa fa-fw fa-globe"></i>
                  
                  微博
                </a>
              </span>
            
              <span class="links-of-author-item">
                <a href="http://www.zhihu.com/people/uiste" target="_blank" rel="external nofollow" title="知乎">
                  
                    <i class="fa fa-fw fa-globe"></i>
                  
                  知乎
                </a>
              </span>
            
          
        </div>

        
        

        
        
          <div class="links-of-blogroll motion-element links-of-blogroll-inline">
            <div class="links-of-blogroll-title">
              <i class="fa  fa-fw fa-globe"></i>
              友情链接
            </div>
            <ul class="links-of-blogroll-list">
              
                <li class="links-of-blogroll-item">
                  <a href="http://www.uiste.com" title="uiste 个人博客" target="_blank">uiste 个人博客</a>
                </li>
              
                <li class="links-of-blogroll-item">
                  <a href="http://uiste.github.io" title="uiste 技术博客" target="_blank">uiste 技术博客</a>
                </li>
              
            </ul>
          </div>
        

      </section>

      

    </div>
  </aside>


        
      </div>
    </main>

    <footer id="footer" class="footer">
      <div class="footer-inner">
        <div class="copyright" >
  
  &copy;  2015 - 
  <span itemprop="copyrightYear">2016</span>
  <span class="with-love">
    <i class="fa fa-heart"></i>
  </span>
  <span class="author" itemprop="copyrightHolder">uiste</span>
</div>

<div class="powered-by">
  由 <a class="theme-link" href="http://hexo.io" rel="external nofollow">Hexo</a> 趋势动力
</div>
<!--站长统计-->
<script src="https://s95.cnzz.com/z_stat.php?id=1259894121&web_id=1259894121" language="JavaScript"></script>
<!--<div class="theme-info">
  Theme -
  <a class="theme-link" href="https://github.com/iissnan/hexo-theme-next" rel="external nofollow">
    NexT.Mist
  </a>
</div> -->
&nbsp;&nbsp;&nbsp;本站总点击 <span id="busuanzi_value_site_pv"></span> 次
&nbsp;&nbsp;&nbsp;|&nbsp;&nbsp;&nbsp;您是第 <span id="busuanzi_value_site_uv"></span> 位访客

<script async src="https://dn-lbstatics.qbox.me/busuanzi/2.3/busuanzi.pure.mini.js">
</script>

<script>
(function(){
    var bp = document.createElement('script');
    bp.src = '//push.zhanzhang.baidu.com/push.js';
    var s = document.getElementsByTagName("script")[0];
    s.parentNode.insertBefore(bp, s);
})();
</script>


        

        
      </div>
    </footer>

    <div class="back-to-top">
      <i class="fa fa-arrow-up"></i>
    </div>
  </div>

  

<script type="text/javascript">
  if (Object.prototype.toString.call(window.Promise) !== '[object Function]') {
    window.Promise = null;
  }
</script>









  



  
  <script type="text/javascript" src="/vendors/jquery/index.js?v=2.1.3"></script>

  
  <script type="text/javascript" src="/vendors/fastclick/lib/fastclick.min.js?v=1.0.6"></script>

  
  <script type="text/javascript" src="/vendors/jquery_lazyload/jquery.lazyload.js?v=1.9.7"></script>

  
  <script type="text/javascript" src="/vendors/velocity/velocity.min.js?v=1.2.1"></script>

  
  <script type="text/javascript" src="/vendors/velocity/velocity.ui.min.js?v=1.2.1"></script>

  
  <script type="text/javascript" src="/vendors/fancybox/source/jquery.fancybox.pack.js?v=2.1.5"></script>


  


  <script type="text/javascript" src="/js/src/utils.js?v=5.0.1"></script>

  <script type="text/javascript" src="/js/src/motion.js?v=5.0.1"></script>



  
  

  

  


  <script type="text/javascript" src="/js/src/bootstrap.js?v=5.0.1"></script>



  

  
    
  

  <script type="text/javascript">
    var duoshuoQuery = {short_name:"uiste"};
    (function() {
      var ds = document.createElement('script');
      ds.type = 'text/javascript';ds.async = true;
      ds.id = 'duoshuo-script';
      ds.src = (document.location.protocol == 'https:' ? 'https:' : 'http:') + '//static.duoshuo.com/embed.js';
      ds.charset = 'UTF-8';
      (document.getElementsByTagName('head')[0]
      || document.getElementsByTagName('body')[0]).appendChild(ds);
    })();
  </script>

  
    
    <script src="/vendors/ua-parser-js/dist/ua-parser.min.js?v=0.7.9"></script>
    <script src="/js/src/hook-duoshuo.js"></script>
  






  
  
  <script type="text/javascript">
    // Popup Window;
    var isfetched = false;
    // Search DB path;
    var search_path = "search.xml";
    if (search_path.length == 0) {
       search_path = "search.xml";
    }
    var path = "/" + search_path;
    // monitor main search box;

    function proceedsearch() {
      $("body").append('<div class="popoverlay">').css('overflow', 'hidden');
      $('.popup').toggle();

    }
    // search function;
    var searchFunc = function(path, search_id, content_id) {
    'use strict';
    $.ajax({
        url: path,
        dataType: "xml",
        async: true,
        success: function( xmlResponse ) {
            // get the contents from search data
            isfetched = true;
            $('.popup').detach().appendTo('.header-inner');
            var datas = $( "entry", xmlResponse ).map(function() {
                return {
                    title: $( "title", this ).text(),
                    content: $("content",this).text(),
                    url: $( "url" , this).text()
                };
            }).get();
            var $input = document.getElementById(search_id);
            var $resultContent = document.getElementById(content_id);
            $input.addEventListener('input', function(){
                var matchcounts = 0;
                var str='<ul class=\"search-result-list\">';
                var keywords = this.value.trim().toLowerCase().split(/[\s\-]+/);
                $resultContent.innerHTML = "";
                if (this.value.trim().length > 1) {
                // perform local searching
                datas.forEach(function(data) {
                    var isMatch = true;
                    var content_index = [];
                    var data_title = data.title.trim().toLowerCase();
                    var data_content = data.content.trim().replace(/<[^>]+>/g,"").toLowerCase();
                    var data_url = data.url;
                    var index_title = -1;
                    var index_content = -1;
                    var first_occur = -1;
                    // only match artiles with not empty titles and contents
                    if(data_title != '' && data_content != '') {
                        keywords.forEach(function(keyword, i) {
                            index_title = data_title.indexOf(keyword);
                            index_content = data_content.indexOf(keyword);
                            if( index_title < 0 && index_content < 0 ){
                                isMatch = false;
                            } else {
                                if (index_content < 0) {
                                    index_content = 0;
                                }
                                if (i == 0) {
                                    first_occur = index_content;
                                }
                            }
                        });
                    }
                    // show search results
                    if (isMatch) {
                        matchcounts += 1;
                        str += "<li><a href='"+ data_url +"' class='search-result-title'>"+ data_title +"</a>";
                        var content = data.content.trim().replace(/<[^>]+>/g,"");
                        if (first_occur >= 0) {
                            // cut out 100 characters
                            var start = first_occur - 20;
                            var end = first_occur + 80;
                            if(start < 0){
                                start = 0;
                            }
                            if(start == 0){
                                end = 50;
                            }
                            if(end > content.length){
                                end = content.length;
                            }
                            var match_content = content.substring(start, end);
                            // highlight all keywords
                            keywords.forEach(function(keyword){
                                var regS = new RegExp(keyword, "gi");
                                match_content = match_content.replace(regS, "<b class=\"search-keyword\">"+keyword+"</b>");
                            });

                            str += "<p class=\"search-result\">" + match_content +"...</p>"
                        }
                        str += "</li>";
                    }
                })};
                str += "</ul>";
                if (matchcounts == 0) { str = '<div id="no-result"><i class="fa fa-frown-o fa-5x" /></div>' }
                if (keywords == "") { str = '<div id="no-result"><i class="fa fa-search fa-5x" /></div>' }
                $resultContent.innerHTML = str;
            });
            proceedsearch();
        }
    });}

    // handle and trigger popup window;
    $('.popup-trigger').click(function(e) {
      e.stopPropagation();
      if (isfetched == false) {
        searchFunc(path, 'local-search-input', 'local-search-result');
      } else {
        proceedsearch();
      };

    });

    $('.popup-btn-close').click(function(e){
      $('.popup').hide();
      $(".popoverlay").remove();
      $('body').css('overflow', '');
    });
    $('.popup').click(function(e){
      e.stopPropagation();
    });
  </script>


  

  

  

</body>
</html>
